I do a lot of work with MySQL and I’ve had this reoccuring problem were I need to find some text in a table and replace it with new text. Like say I have a table of data that talks about dogs and I want to replace every occurrence of dog with cat. The old way I would search the entire table to find all rows that have the word dog in it, then that would give me a list to manually update each row. I’d then rerun the query to see if I missed any.
Recently I found that MySQL supports a command called what else but “replace”. So let’s say I have a table called “news” and in the table is a column called “content” and I wanted to replace all references of “dog” with “cat”, here is an example query.
update news set content = replace(content, “dog”, “cat”);
Very simple, it tells MySQL to replace the “content” field with what’s in the “content” field but replace “dog” with “cat”.
