How can I search/replace strings in MySQL?
MySQL lets you replace all occurrences of a character or string of characters in a table with some other character or string.
UPDATE table SET field1 = REPLACE(field1, 'replace_that', 'with_this'), field2 = REPLACE(field2, 'and_that', 'with_this')
As an example, let’s say that you have a pets table and you want to change every cat into a dog:
UPDATE pets SET species = REPLACE(species, 'cat', 'dog')
This feature is also useful for transposing characters from one encoding to another. Here’s how to change Windows-style CRLF line endings into Unix-style LF line endings:
UPDATE users SET bio = REPLACE(bio, CONCAT(CHAR(13), CHAR(10)), CHAR(10))
Of course, you don’t have to replace all occurrences in a table if you don’t want to. Just supply a WHERE clause and only those rows will be affected.