Find and replace in MySQL

This week I needed to do some find and replace on a column value in a MySQL table. I had a table that contained a column of the form name (id) and I needed to replace the id part with a fixed value.

When I started looking into a solution I found that MySQL supports regular expressions, but sadly enough they aren't supported in the replace statement. Since I hadn't got a lot of time I had to do it quick and dirty by looking up the positions of the parentheses and then use those positions in combination with the substring function. That resulted in the following query:

UPDATE table SET column = REPLACE(column, SUBSTRING(column, LOCATE('(', column), LOCATE(')', column) - LOCATE('(', column) + 1), 'newvalue')

Just replace table, column and newvalue with the correct values and you're good to go. It also might be a good idea to try the query first in the following form just to be safe, as this won't change anything yet:

SELECT column, REPLACE(column, SUBSTRING(column, LOCATE('(', column), LOCATE(')', column) - LOCATE('(', column) + 1), 'newvalue') FROM table