如何在MySQL中执行正则表达式替换? [英] How to do a regular expression replace in MySQL?

问题描述

我有一张约有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()_ .\-]

现在， MySQL中是否有一个函数可以让您通过正则表达式替换?我正在寻找与REPLACE()函数类似的功能-简化示例如下:

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 ，但是那些仅检查是否是否存在匹配项，而不是匹配项.

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

(我可以从PHP脚本中执行"SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'"，先执行preg_replace，然后执行"UPDATE foo ... WHERE pkey_id=..."，但这看起来像是最后的手段，而且很丑陋) hack)

(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函数.

With MySQL 8.0+ you could use natively REGEXP_REPLACE function.

12.5.2正则表达式:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

将字符串 expr 中与模式 pat 指定的正则表达式匹配的匹配项替换为替换字符串 repl ，并返回结果细绳.如果 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)重新实现了对正则表达式的支持，该组件提供了完整的Unicode支持并且是多字节安全的. REGEXP_LIKE()函数以REGEXP和RLIKE运算符的方式执行正则表达式匹配，它们现在是该函数的同义词. 此外， REGEXP_INSTR() ， REGEXP_REPLACE() 和 REGEXP_SUBSTR() 功能可用于查找匹配位置并执行子字符串替换和提取.

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演示

这篇关于如何在MySQL中执行正则表达式替换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！