i know, i know, dynamic sql=bad idea...

anyways, what is the correct way to write this statement in dynamic sql- i
can't seem to get it right..

im just trying to replace all double quotes with single quotes in my
resultset-


declare @sql as varchar(255)
set @sql + "select replace(t1.log_text, '"', ''') as log_text" + space(1) +
char(13) + char(10)
set @sql + @sql "from myLog"

thanks,

jt

JT wrote:
Show quoteHide quoteHere is what you would need to do without dynamic sql:
declare @s varchar(50)
set @s='9" hammer'
print @s
set @s=replace(@s,'"','''')
print @s

one single quote, one double-quote,one single quote, the comma, then 4
single quotes.

So the idea is to create the same number of single quotes in the statement
assigned to @sql. You do this by doubling up the single quotes:
set @sql = 'select replace(t1.log_text, ''"'', '''''''') as log_text' +

so there are 2 single-quotes, 1 double-quote,two single quotes, a comma,
then 8 single quotes

HTH,
Bob Barrows
PS. Stop using dynamic sql ;-)

JT (jt@nospam.com) writes:
Show quoteHide quoteLooks like you are running with SET QUOTED_IDENTIFIER OFF, which, well it
is practical when you work with dyanmic SQL, but there are some contexts
where QUOTED_IDENTIFIER is required to be on, and it's on by default in
most places as well.

Anyway:

declare @sql as nvarchar(255)
set @sql = 'select replace(t1.log_text, ''"'', @q) as log_text from my_log'
EXEC sp_executesql @sql, N'@q char(1)', @q = ''''

Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column
Question about referencing UDFs
Torn over a normalizing decision
Mulitple Create Views in Query Batch