mysql如何替换列中的字符,MySQL - 替换列中的字符

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?

解决方案

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,'"','\'')