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?

You really need to read up on using Paramaterized queries. This will keep you from making your application vulnerable to SQL Injection — John Hartsock
– John Hartsock, Commented Oct 12, 2011 at 0:42
Note that in ASP, using """" renders as a value of a single quote ("), not a double quote (""), because inside of a string, a quote is represented (escaped) by two quotes. — mellamokb
– mellamokb, Commented Oct 12, 2011 at 0:47
That is next on my list to learn John, yes. I've got a regular expression function which checks input values and querystrings for malicious content but I'm sure that isn't that safe... — TheCarver
– TheCarver, Commented Oct 12, 2011 at 0:50
Thanks mellamokb but as I said in my question, I do understand why they changed to single quotes. What I now need to know, is how I reference them in the replace function. Do I replace doubles or singles now? — TheCarver
– TheCarver, Commented Oct 12, 2011 at 0:52

mellamokb · Accepted Answer · 2011-10-12 03:29:55Z

166

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

answered Oct 12, 2011 at 3:29

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheCarver Over a year ago

Thanks very much!! I get it now, select would print the conversion but update will actually update the DB...

Silvan Over a year ago

To check without DB change, try before: SELECT * FROM photos WHERE LOCATE('"','\')>0

Ramon Canales Over a year ago

This should not be the accepted answer anymore... REPLACE function works just fine on MySQL

Umar Adil · Accepted Answer · 2015-02-11 11:01:29Z

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

⁣⁣⁣(⁣⁣⁣⊙⁣⁣⁣.⁣⁣⁣☉⁣⁣⁣)⁣⁣⁣⁣⁣
OMG you are the best guy in stackoverflow! This answer is amazing, thanks God I read it, now my life it's different and happier

Nathan · Accepted Answer · 2011-10-12 00:51:05Z

5

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.

answered Oct 12, 2011 at 0:51

Nathan

1,5201 gold badge12 silver badges22 bronze badges

3 Comments

TheCarver Over a year ago

I ran this on one row, checked the row after and it still had ( " ). I'm editing my question to show you the other variations I tried

Nathan Over a year ago

does the caption have other values other than the quotes?

TheCarver Over a year ago

Example caption [Brad Pitt is wearing Angelina Jolie"s bikini]

mellamokb · Accepted Answer · 2011-10-12 03:27:12Z

0

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

edited Oct 12, 2011 at 3:27

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

answered Oct 12, 2011 at 0:43

Esselans

1,5474 gold badges24 silver badges45 bronze badges

3 Comments

TheCarver Over a year ago

That didn't work, I ran it on one row, checked the row and it still contains ( " )

Esselans Over a year ago

SUBSTRING(data, 1, LEN(data)) -i'm not sure if it's 1 or 2 in the parameter, but this will cut the first and the last char from a string, so "whatever" or 'whatever', should output as whatever.

TheCarver Over a year ago

No good unfortunately, I have data like [Kate Moss's], [O'Sullivan] and ['whatever']

Collectives™ on Stack Overflow

MySQL - Replace Character in Columns

4 Answers 4

3 Comments

4 Comments

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related