Remove ' from Character Field

[gkrenton]

I'm creating some data to export to a flat text file interface. I've been asked to do either remove the single quotes that reside in the description field OR add double qoutes around the output.

select
1 as Division,
substring (ca_id from 1 for 1) as dept,
(ca_desc)
from category
where ca_id like '%000'

ca_desc contains words like
Men's or Women's. (it's a char(30) field)
I need to supply this data either as
Mens or "Men's" or Womens or "Women's"

I've tried ""|(ca_desc)|"" but that gives me
|(ca_desc)| as my result vs Men's or Women's etc.

Any ideas other than creating a secondary table feed by this data or a really long case statement?

- thanks

gina

 

[earthandfire]

Have you looked at the Replace function in BOL


Hope this helps.

 

[gmmastros]

You can easily replace the single quote character with an empty string, or even add a quote symbol in front of and behind the data. Like this...

[COLOR=blue]select[/color] 1 [COLOR=blue]as[/color] Division, 
       [COLOR=#FF00FF]substring[/color] (ca_id [COLOR=blue]from[/color] 1 [COLOR=blue]for[/color] 1) [COLOR=blue]as[/color] dept,   
       [COLOR=#FF00FF]Replace[/color](ca_desc, [COLOR=red]''''[/color], [COLOR=red]''[/color]) [COLOR=blue]As[/color] ca_desc
[COLOR=blue]from[/color]   category 
[COLOR=blue]where[/color]  ca_id  like  [COLOR=red]'%000'[/color]


[COLOR=blue]select[/color] 1 [COLOR=blue]as[/color] Division, 
       [COLOR=#FF00FF]substring[/color] (ca_id [COLOR=blue]from[/color] 1 [COLOR=blue]for[/color] 1) [COLOR=blue]as[/color] dept,   
       [COLOR=red]'"'[/color] + ca_desc + [COLOR=red]'"'[/color] [COLOR=blue]As[/color] ca_desc
[COLOR=blue]from[/color]   category 
[COLOR=blue]where[/color]  ca_id  like  [COLOR=red]'%000'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

To get rid of the ', check out REPLACE.

REPLACE(ca_desc, ''', '')

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Shoot, took too long to type and got beat to the finish line. AND I missed the extra ' that was needed in the replace.

-SQLBill

Posting advice: FAQ481-4875

 

[gkrenton]

Holy Cow - you're all fast! All workable solutions - I tried replace but didnt' have enough ' based on these examples.

Thanks - you've all made my day a bit brighter.

gina