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”.

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%";