Replacing single to double quotes in a REPLACE statement

Transact-SQL
1

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

2

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 ...

3 
SELECT Replace(MyColumn, '''', '"')
FROM MyTable;
4

I may be missing something, but won't that convert "'John'" to ""John"" ?

5 
declare @tbl table(NameSingleQuoted varchar(30));
insert @tbl values('''John''');
select NameSingleQuoted,Replace(NameSingleQuoted, '''', '"') NameDoubleQuoted from @tbl;

6

This was the bit in the O/P I was referring to:

7

Sorry, I missed that.

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;

8

The point here is you need to test the data in each row to see what needs to change.