Search and replace in a MySQL table
27th October 2009
This query performs a table-wide search-and-repalce:
UPDATE `table_name` SET `table_field` = REPLACE(`table_field`,’string to search for and replace’,'replacement string’);
If you need a database-wide search-and-replace, you could try this script (I haven’t tested/used it myself).
Beware of the following gotchas:
- wrong query syntax may ruin the field you are performing replace on, so always backup first!
- be sure to provide “search-for” string as specific as possible, or you will get unexpected replacements (e.g. replacing mini with little will also convert all minivans into littlevans); also, do use WHERE clause when necessary to limit the number of rows modified
- the function in the example is case-sensitive, so replacing all minivans with vehicles won’t replace Minivans. However, I believe there exists a case-insensitive version of REPLACE function