How do I replace part of a text in SQL?

Do you have an existing SQL database and want to search and replace all occurrences of a text in a table's column?

All you need to do is use the REPLACE method available on all SQL database management systems by specifying the desired column, the old value, and then the new one.

Here's an example query that's easy to set up:

UPDATE `table`
SET `column` = REPLACE(`column`, 'old_value', 'new_value')
WHERE `column` LIKE '%old_value%';

The % signs in the LIKE clause are the equivalent of ".*" in a regular expression!

Note that here the WHERE condition is almost redundant, since REPLACE won't modify the field if the searched string isn't already present.

Nevertheless, this allows you to select only a subset of the table's rows, thus speeding up processing. The difference is negligible on a small table, but once you start dealing with tens of thousands of rows, it becomes essential!

Exception: Serialized fields

If you store serialized fields such as arrays in PHP, be careful not to include them in your replacement query, as otherwise you risk corrupting your data.

Indeed, the transformed string would no longer have the same length before and after, making deserialization impossible.