MySQL - Replace Character in Columns

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?

maybe I'd go by this.

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


I used singlequotes because that's the one that registers string expressions in MySQL, or so I believe.


Hope that helps.

If you have "something" and need 'something', use `replace(col, "\"", "\'")` and viceversa.

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:

[To see links please register here]

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