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
Analyzing problem and generating solution…
Was this solution helpful?