I need help to do a replace statement where i need to replace some words from single to double quotes e.g., from 'John' to "John" and in some cases there are even "'John'" to "John". I used
update client set fname= REPLACE(fname, '"''John''"', '''John''')
the result was 'John' i need "John"
I want to be sure i do this right to avoid screwing things up...thanks
I would use SELECT to make sure that the Replace was what I wanted and then use UPDATE once I had tested it. Something like this:
-- UPDATE C
-- SET
SELECT [Old]=fname,
fname= REPLACE(REPLACE(
fname
, '"''John''"', '"John"')
, '''John''', '"John"')
FROM client AS C
Once the test is completed Comment-Out the SELECT line and Comment-In the UPDATE and SET lines.
You could also take a copy of the table, before you start, in order that you can "copy it back" if the update fails
SELECT *
INTO TEMP_client_20170614
FROM client
and then
DROP TABLE TEMP_client_20170614
once you are sure everything has worked OK. (I use TEMP prefix, and a date Suffix for this type of temporary table so when I see the table is still there in a couple of months!!! I can say "That's TEMP and OLD ENOUGH that I can't possibly need it any more, so I can DROP it now ...
Here's another pass just to get you strated. The Idea is to add some test for what kind of correction needs to be made using the CASE statement to "control flow" what to do.
declare @tbl table(NameStr varchar(30));
insert @tbl values('''John'''),('"''John''"');
select
NameStr
, case when charindex('"''',NameStr)>0 then Replace(Replace(NameStr, '"''', '"'), '''"', '"') else Replace(NameStr, '''', '"') end NameDoubleQuoted
from @tbl;