sql - MySQL - Replace Character in Columns

Question

Being a self-taught newbie, I created a large problem for myself. Before inserting data in to my database, I've been converting apostrophes (') in a string, to double quotes (""), instead of the required back-slash and apostrophe ('), which MySQL actually requires.

Before my table grows more than the 200,000 rows it already is, I thought it was best to rectify this issue immediately. So I did some research and found the SQL REPLACE function, which is great, but I'm now confused.

In ASP, I was doing this:

str = Replace(str,"'","""")

If I look at my database in SQL Workbench, the symbol I converted is now a single quote ("), which has confused me a little. I understand why it changed from double to single, but I don't know which one I'm meant to be changing now.

To go through and rectify my problem using SQL REPLACE, do I now convert single quotes (") to back-slash and apostrophes (') or do I convert double quotes ("") to back-slash and apostrophes (')?

For example, this:

SQL = " SELECT REPLACE(myColumn,"""","'") FROM myTable "

or this:

SQL = " SELECT REPLACE(myColumn,""","'") FROM myTable "

I hope I explained myself well, any suggestions gratefully received as always. Any queries about my question, please comment.

Many thanks

-- UPDATE --

I have tried the following queries but still fail to change the ( " ) in the data:

SELECT REPLACE(caption,'"',''') FROM photos WHERE photoID = 3371
SELECT REPLACE(caption,'"',''') FROM photos WHERE photoID = 3371
SELECT REPLACE(caption,'""',''') FROM photos WHERE photoID = 3371

Yet if I search:

SELECT COUNT(*) FROM photos WHERE caption LIKE '%"%'

I get 16,150 rows.

-- UPDATE 2 --

Well, I have created a 'workaround'. I managed to convert an entire column pretty quickly writing an ASP script, using this SQL:

SELECT photoID, caption FROM photos WHERE caption LIKE '%""%';

and then in ASP I did:

caption = Replace(caption,"""","'")

But I would still like to know why I couldn't achieve that with SQL?

Answer 1

Just running the SELECT statement will have no effect on the data. You have to use an UPDATE statement with the REPLACE to make the change occur:

UPDATE photos
   SET caption = REPLACE(caption,'"',''')

Here is a working sample: http://sqlize.com/7FjtEyeLAh