問題描述
我有一個大約有 50 萬行的表格；varchar(255) UTF8 列 filename 包含文件名；
I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name;
我試圖從文件名中去除各種奇怪的字符 - 我想我會使用一個字符類:[^a-zA-Z0-9()_ .\-]
I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-]
現(xiàn)在，MySQL 中是否有一個函數(shù)可以讓您通過正則表達式進行替換?我正在尋找與 REPLACE() 函數(shù)類似的功能 - 簡化示例如下:
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"
我知道REGEXP/RLIKE，但那些只檢查if是否匹配，而不是什么匹配.
I know about REGEXP/RLIKE, but those only check if there is a match, not what the match is.
(我可以做一個SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'" 從 PHP 腳本中，執(zhí)行 preg_replace 然后"UPDATE foo ... WHERE pkey_id=..."，但這看起來像是最后的緩慢&丑陋的黑客)
(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)
推薦答案
使用 MySQL 8.0+ 你可以原生使用 REGEXP_REPLACE 函數(shù).
With MySQL 8.0+ you could use natively REGEXP_REPLACE function.
12.5.2 正則表達式:
REGEXP_REPLACE(expr, pat, repl[, pos[,occurrence[, match_type]]])
用替換字符串repl替換字符串expr中匹配模式pat指定的正則表達式的出現(xiàn)，并返回結(jié)果細繩.如果expr、pat或repl為NULL，則返回值為NULL.
Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.
和正則表達式支持:
以前，MySQL 使用 Henry Spencer 正則表達式庫來支持正則表達式運算符(REGEXP、RLIKE).
Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators (
REGEXP,RLIKE).
正則表達式支持已使用 Unicode 國際組件 (ICU) 重新實現(xiàn)，該組件提供完整的 Unicode 支持并且是多字節(jié)安全的.REGEXP_LIKE() 函數(shù)以 REGEXP 和 RLIKE 運算符的方式執(zhí)行正則表達式匹配，它們現(xiàn)在是該函數(shù)的同義詞.此外， REGEXP_INSTR()、 REGEXP_REPLACE() 和 REGEXP_SUBSTR() 函數(shù)可用于查找匹配位置并分別執(zhí)行子字符串替換和提取.
Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR(), REGEXP_REPLACE(), and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively.
SELECT REGEXP_REPLACE('Stackoverflow','[A-Zf]','-',1,0,'c');
-- Output:
-tackover-low
DBFiddle 演示
這篇關(guān)于如何在 MySQL 中進行正則表達式替換?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！