How Can I insert an Apostrophe in a field of a SQL table?

[LittleNick]

Hi All,
Let say I have a SQL table named tblSPcodes in which all fraction numbers are stored as text like this:
SCode
255.20
332.01
227.1
732.10
When I export this text column to Excel it will make it look like this
SCode
255.2
332.01
227.1
732.1
But if I format the cell 2 decimal places in Excel it will look like this
SCode
255.20
332.01
227.10 (extra 0 that can not be in there)
732.10
It will add the 0 to the end of 227.1 as 227.10 which is not what it's in SQL table. So What I plan to do is to insert (or update the column) the apostrophe (') in front of the codes so that when it export to excel the (') will tell it to keep it as text format and therefore exactly the way it looks in Excel as in tblSPCode. But the problem is I am having hard time to insert the apostrophe (')
update tblSPCode
set Scode = ''' + SCode
will not work because of this (''').
Any suggestion is very appreciated

 

[bborissov]

You should have an extra quote there

update tblSPCode
      set Scode = '''' + SCode

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[simian336]

If you don't want a second zero why format the cell to 2 decimal places?

Also if you just formatted the cell as text in Excel it would have the same effect.

Simi

 

[Gooser]

You could do something like this:

UPATE <tablename>
   SET <columnname> = CAST(char(39) + <columnname> AS varchar(32))

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[Gooser]

...Though, after further thougth, it would be better to do it at the time you select it.

SELECT <...columns...>
     , CAST(char(39) + <columnname> AS varchar(32))
     , <...columns...>
   FROM <tablename>

Hope this helps.

v/r
Gooser