SQL string replaces pattern

Question

I have a table X with a column Y that contains the text as follows:

Click <a href=""http://www.stackoverflow.com"">here</a> to redeem 

I need it to turn into:

 Click <a href="http://www.stackoverflow.com">here</a> to redeem 

i.e. remove an extra pair of quotation marks

Text outside url can be anything.

Is it something like this?

 Update X SET Y = REPLACE(Y, '""%""', '"%"' ); 

Answer 1

REPLACE replaces all occurrences of the second parameter with the third parameter:

 update X set Y = replace(Y, '""', '"') 

Answer 2

 select replace('Click <a href=""http://www.stackoverflow.com"">here</a> to redeem','""','"') 

I use select to check what the update will do before doing the update.

Answer 3

You need to replace "with", so do the following:

 Update X SET Y = REPLACE(Y, '""', '"') 

Answer 4

You need to replace "with", so do the following:

 Update X SET Y = REPLACE(Y, '""', '"') WHERE y LIKE '""%""'; 

Use with a dose of caution because it will also replace text, for example:

 Click <a href="http://www.stackoverflow.com">""here""</a> to redeem 

in

 Click <a href="http://www.stackoverflow.com">"here"</a> to redeem