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

Answer 2

Replace below characters

~ ! @ # $ % ^ & * ( ) _ + ` - =  { } | [ ] \ : "  ; '  < > ? , .  

with this SQL

SELECT note as note_original,       REPLACE(         REPLACE(             REPLACE(                 REPLACE(                     REPLACE(                         REPLACE(                             REPLACE(                                 REPLACE(                                     REPLACE(                                         REPLACE(                                             REPLACE(                                                 REPLACE(                                                     REPLACE(                                                         REPLACE(                                                             REPLACE(                                                                 REPLACE(                                                                     REPLACE(                                                                         REPLACE(                                                                             REPLACE(                                                                                 REPLACE(                                                                                     REPLACE(                                                                                         REPLACE(                                                                                             REPLACE(                                                                                                 REPLACE(                                                                                                     REPLACE(                                                                                                         REPLACE(                                                                     REPLACE(                                                                         REPLACE(                                                                             REPLACE(                                                                                 REPLACE(                                                                                     REPLACE(                                                                                         REPLACE(                                                                                             REPLACE(note, '\"', ''),                                                                                         '.', ''),                                                                                     '?', ''),                                                                                 '`', ''),                                                                             '<', ''),                                                                         '=', ''),                                                                     '{', ''),                                                                                                         '}', ''),                                                                                                     '[', ''),                                                                                                 ']', ''),                                                                                             '|', ''),                                                                                         '\'', ''),                                                                                     ':', ''),                                                                                 ';', ''),                                                                             '~', ''),                                                                         '!', ''),                                                                     '@', ''),                                                                 '#', ''),                                                             '$', ''),                                                         '%', ''),                                                     '^', ''),                                                 '&', ''),                                             '*', ''),                                         '_', ''),                                     '+', ''),                                 ',', ''),                             '/', ''),                         '(', ''),                     ')', ''),                 '-', ''),             '>', ''),         ' ', '-'),     '--', '-') as note_changed FROM invheader