LMSouq
database Open

How to do a regular expression replace in MySQL?

PI
Piskvor
1 month ago
3 views
Problem Description
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][1], 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) [1]: https://stackoverflow.com/a/6943142/19746

AI-Generated Solution

Powered by LMSouq AI · GPT-4.1-mini

✓ Solution Ready
Analyzing problem and generating solution…
Was this solution helpful?
Back to Knowledge Base