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.