String functions not helping
-
Hi
I'm using a cursor to read values into a char variable, then edit it and place it back into the table. However, if the string is something like Earl's Diner, it doesn't re-insert the row. The offending character needs to be changed from ' to '', but I cannot seem to achieve this with the replace function. Is there a way for me to search through the string and replace ' with '' before trying to add it to the table?
Thanks
B
-
It would be helpful, if you can provide the code, along with sample data and desired output.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
try this
select replace('tester''s', '''','''''')
>tester''s
>(1 row(s) affected)
you need to double (escape) the single quotes so SQL Server can parse them
cheers
dbgeezer
-
have you tried replacing using ascii ?! 34 for 39 or some such ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Hi
Thank you very much for your help. I have added the replace function in the select that forms part of my cursor
declare cr_test cursor for
select replace(location,char(39),char(34)) from test_table
open cr_test
fetch next from cr_test into @location
while @@fetch_status = 0
begin
print @location
fetch next from cr_test into @location
end
close cr_test
deallocate cr_test
and it works PERFECTLY with my dynamic sql statements! 🙂
Thanks again!
Brenchia
-
Hi
Thank you very much for your help. I have added the replace function in the select that forms part of my cursor
declare cr_test cursor for
select replace(location,char(39),char(34)) from test_table
open cr_test
fetch next from cr_test into @location
while @@fetch_status = 0
begin
print @location
fetch next from cr_test into @location
end
close cr_test
deallocate cr_test
and it works PERFECTLY with my dynamic sql statements! 🙂
Thanks again!
Brenchia
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply