Search and Replace in MySQL

While converting a Drupal site to a Joomla site, the need to do a textual search and replace inside a MySQL-table came up. There are, of course, many ways of doing this, but the most simple way I could figure out was to use a query like this:

update the_table set the_column = replace (the_column, 'orange', 'apple');

which tells MySQL to process all rows in the table “the_table” and update the column “the_column” with the return value of the MySQL replace() function. In this example, it would replace all occurrences of “orange” with “apple”.

This query works on the whole table, one could obviously add a limiting WHERE clause to the end of the query.

This isn’t rocket science and probably old “news” to most, but perhaps someone will come across it and find it useful 🙂

Leave a Comment

Notify me of followup comments via e-mail. You can also subscribe without commenting.