Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

LittleNick

Technical User
Jun 26, 2009
55
US
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
 
You should have an extra quote there :)
Code:
update tblSPCode
      set Scode = '''' + SCode

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
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
 
You could do something like this:

Code:
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 [ponder] --me
 
...Though, after further thougth, it would be better to do it at the time you select it.

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

Hope this helps.

v/r
Gooser
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top