How to do a regular expression replace in MySQL?

Question

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name;

I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-]

Now, is there a function in MySQL that lets you replace through a regular expression? I'm looking for a similar functionality to REPLACE() function - simplified example follows:

SELECT REPLACE('stackowerflow', 'ower', 'over');  Output: "stackoverflow"  /* does something like this exist? */ SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-');   Output: "-tackover-low" 

I know about REGEXP/RLIKE, but those only check if there is a match, not what the match is.

(I could do a "SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'" from a PHP script, do a preg_replace and then "UPDATE foo ... WHERE pkey_id=...", but that looks like a last-resort slow & ugly hack)

Answer 1

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.

Answer 2

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