using REPLACE in MySQL Query just isn't working

Question

Quick background, I have a small database with a table named 'songs'. This table holds the title, artist and URL of music I have on my machine. Several of the single quotes have been dropped from the track title (for example, the don't is stored as dont) and I'm attempting to replace them. I just cannot get this query to affect any rows:

UPDATE Songs
SET Title = REPLACE (Title, 'dont', 'don\'t')

No love. Isn't this correct syntax? It tells me that 0 rows were updated. If it helps, I'm running version 5.5.27. I know there are a couple hundred rows with improper donts in there... I'm about to dump the results into Notepad and do a find/replace on don't and just run an update statement that way, but it's kinda hacky. Any ideas friends?

A couple of sample rows:

"51","Dont Stay Home","311","311 Greatest Hits","Rap","E:\Music\311\311GreatestHits\dontstayhome.mp3"
"229","Dont Turn Around","Ace Of Base","The Very Best Of","Dance","E:\Music\AceofBase\VeryBestOf\03-ace_of_base-dont_turn_around.mp3"

The Fields in order are id, title, artist, album, genre, path

peterm · Accepted Answer · 2013-08-27 03:01:13Z

1

You have to do it like this

UPDATE Songs
    SET Title = REPLACE(Title, 'Dont', 'Don\'t');
                                ^       ^

The reason for that is

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

If you want to replace either case you can do

UPDATE Songs 
   SET Title = REPLACE(REPLACE(Title, 'Dont', 'Don\'t'), 'dont', 'don\'t')
 WHERE Title LIKE '%dont%' -- it makes sense to limit update to only those rows that have dont in it no matter case

Here is SQLFiddle demo

edited Aug 27, 2013 at 3:01

answered Aug 27, 2013 at 2:56

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

Aneal Khimani Over a year ago

One internet for you good sir! Thank you. I totally missed the case sensitive part of that function description. I feel like a tool now! I'd upvote you too if I had enough of a rep! Now I'm off to answer some questions of my own so I can upvote you at a later date!

Gordon Linoff · Accepted Answer · 2013-08-27 02:39:55Z

0

The first thought is that there might be some invisible characters. What does the following return?

select *
from songs
where title like '%dont%';

EDIT:

I didn't notice this at first. The problem is the space after the function name. Try this:

UPDATE Songs
    SET Title = REPLACE(Title, 'dont', 'don\'t');

This is explained in the documentation:

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

EDIT II:

I don't know if the collation has an effect. But you can also try using double quotes as the string delimiter:

UPDATE Songs
    SET Title = REPLACE(Title, 'dont', "don't");

edited Aug 27, 2013 at 2:39

answered Aug 27, 2013 at 2:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Aneal Khimani Over a year ago

I'm sure there are rows that need updating. Using this query I got back 99 rows: SELECT COUNT(*) FROM Songs WHERE Title LIKE '%dont%'

Aneal Khimani Over a year ago

Tried that as well. Tried all caps on the function name, tried lower case. Tried matching case for Title~title, tried space after function, tried no space after function. Tried with delimiter character ; afterwards, tried without.

nyzm Over a year ago

how about a couple of sample rows that the above query didn't update?

Aneal Khimani Over a year ago

If it helps, that field is a varchar(255) and holds latin1_swedish_ci collation (I don't believe I set this at design time).

Aneal Khimani Over a year ago

Also gave the double quotes as string delimiter, without success. Thanks for the help and advice so far...

Collectives™ on Stack Overflow

using REPLACE in MySQL Query just isn't working

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related