How to do a regular expression replace in MySQL?

My brute force method to get this to work was just:

1. Dump the table - mysqldump -u user -p database table > dump.sql
2. Find and replace a couple patterns - find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;, There are obviously other perl regeular expressions you could perform on the file as well.
3. Import the table - mysqlimport -u user -p database table < dump.sql

If you want to make sure the string isn't elsewhere in your dataset, run a few regular expressions to make sure they all occur in a similar environment. It's also not that tough to create a backup before you run a replace, in case you accidentally destroy something that loses depth of information.

MySQL 8.0+:

You can use the native REGEXP_REPLACE function.

Older versions:

You can use a user-defined function (UDF) like mysql-udf-regexp.

If you are using MariaDB or MySQL 8.0, they have a function

REGEXP_REPLACE(col, regexp, replace)

See MariaDB docs and PCRE Regular expression enhancements

Note that you can use regexp grouping as well (I found that very useful):

SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')

returns

over - stack - flow