Double quotes replacement

[NickName]

Hi,

It seems to be simple, however, it stumbles me.
how to replace all the double quotes (") within the following
sentence (or a column) with single quotes ('),

colA = this is a freaking "silly" thing to do
into
colA this is a freaking 'silly' thing to do

Select Replace(colA,'" ',''')[color=blue]
>From tblXYZ[/color]

won't work,

Select Replace(colA,'" ',"'")[color=blue]
>From tblXYZ[/color]

won't work neither.

How come? Thanks.

[Dan Guzman]

Re: Double quotes replacement

You need to specify 2 single quotes within the literal string when 1 quote
is desired. Try:

Select Replace(colA,'" ','''')
From tblXYZ

--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@rock.co m> wrote in message
news:1125889758 .581518.99700@g 49g2000cwa.goog legroups.com...[color=blue]
> Hi,
>
> It seems to be simple, however, it stumbles me.
> how to replace all the double quotes (") within the following
> sentence (or a column) with single quotes ('),
>
> colA = this is a freaking "silly" thing to do
> into
> colA this is a freaking 'silly' thing to do
>
> Select Replace(colA,'" ',''')[color=green]
>>From tblXYZ[/color]
>
> won't work,
>
> Select Replace(colA,'" ',"'")[color=green]
>>From tblXYZ[/color]
>
> won't work neither.
>
> How come? Thanks.
>[/color]

[NickName]

Re: Double quotes replacement

Thank you very much, Dan, works perfect.

On a related note, BOL does not cover it, how could one find a solution
to a problem similar to this one without resorting to this NG?

[Dan Guzman]

Re: Double quotes replacement

I found this from the 'quotation marks' entry in the Books Online index.
From the 'Using char and varchar data' topic:

<Excerpt href="acdata.ch m::/ac_8_con_03_7mc h.htm">
When using single quotation marks to delimit a character constant that
contains an embedded single quotation mark, use two single quotation marks
to represent the embedded single quotation mark, for example:

SET @MyCharVar = 'O''Leary'
</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@rock.co m> wrote in message
news:1125967013 .923190.247670@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> Thank you very much, Dan, works perfect.
>
> On a related note, BOL does not cover it, how could one find a solution
> to a problem similar to this one without resorting to this NG?
>[/color]

[NickName]

Re: Double quotes replacement

Man, I can't read, ok, not careful, thanks.