MySQL - Replace Character in Columns
MysqlSqlDatabaseMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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
Solution 3 - Mysql
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.
Solution 4 - Mysql
If you have "something" and need 'something', use replace(col, "\"", "\'") and viceversa.