how to search and replace within a column

let’s say you’ve got a text or varchar column in mysql and you want to replace some portion of the field with new text.

for instance maybe you want to change all instances of the word “colour” with “color”.

column before becomes column after
a purple colour a purple color
colour green color green
bluish colour bluish color

last week at work we got just such a request, that in the past i’d probably solved with a gui search and replace interface (like Microsoft Access) or a small php program that looped through the table rows, modifying and updating each individual field value.

but it seemed like there should be a way to accomplish single column search and replace in one fell swoop, using a single SQL statement. the challenge was on! after quite a bit of “i can’t believe i’ve never written a query to do that before” and thus wondering if such a query even exists, we figured out the solution.

UPDATE table
SET column = REPLACE(column, "from string", "to string")
WHERE column LIKE "%from string%";


New comments are no longer accepted, so if you want to get in touch, send me an email.


I can’t believe you’ve never done that before either.

i know. i was aghast.


Thanks you solved my problem. I forgot to replace the ‘%20’s from my java app… Thanks a lot!


Not a newbie… and it just saved us a load of work here, updated around 600 instances of different US / UK spelling changes

Always nice to have a right-to-the-point example like this rank high in google searches. Thanks.