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