我有一个大约有500k行的表;varchar(255) UTF8列文件名包含一个文件名;
我试图从文件名中剥离出各种奇怪的字符-我想我会使用字符类:[^a- za - z0 -9()_ .\-]
现在，MySQL中是否有一个函数允许你通过正则表达式进行替换?我正在寻找一个类似的功能REPLACE()函数-简化的例子如下:
SELECT REPLACE('stackowerflow', 'ower', 'over');
Output: "stackoverflow"
/* does something like this exist? */
SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-');
Output: "-tackover-low"
我知道REGEXP/RLIKE，但它们只检查是否有匹配，而不检查匹配是什么。
(我可以做一个“SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a- za - z0 -9()_ .\-]'”从一个PHP脚本，做一个preg_replace，然后“更新foo…WHERE pkey_id=…”，但这看起来像一个最后的手段缓慢和丑陋的黑客)
我认为有一个简单的方法来实现这一点，这对我来说很有效。
使用REGEX选择行
SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'
使用REGEX更新行
UPDATE `table_name` SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') WHERE column_name_to_find REGEXP 'string-to-find'
REGEXP参考:
https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
我最近写了一个MySQL函数来使用正则表达式替换字符串。你可以在以下地点找到我的职位:
http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/
下面是函数代码:
DELIMITER $$
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END$$
DELIMITER ;
示例执行:
mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');
下面的一个基本上从左边找到第一个匹配，然后替换它的所有出现(在mysql-5.6中测试)。
用法:
SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');
实现:
DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000),
var_replacement VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
WHILE var_leftmost_match IS NOT NULL DO
IF var_replacement <> var_leftmost_match THEN
SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
ELSE
SET var_leftmost_match = NULL;
END IF;
END WHILE;
RETURN var_replaced;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT '
Captures the leftmost substring that matches the [var_pattern]
IN [var_original], OR NULL if no match.
'
BEGIN
DECLARE var_temp_l VARCHAR(1000);
DECLARE var_temp_r VARCHAR(1000);
DECLARE var_left_trim_index INT;
DECLARE var_right_trim_index INT;
SET var_left_trim_index = 1;
SET var_right_trim_index = 1;
SET var_temp_l = '';
SET var_temp_r = '';
WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
SET var_temp_l = LEFT(var_original, var_left_trim_index);
IF var_temp_l REGEXP var_pattern THEN
WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
IF var_temp_r REGEXP var_pattern THEN
RETURN var_temp_r;
END IF;
SET var_right_trim_index = var_right_trim_index + 1;
END WHILE;
END IF;
SET var_left_trim_index = var_left_trim_index + 1;
END WHILE;
RETURN NULL;
END $$
DELIMITER ;
如果您使用的是MariaDB或MySQL 8.0，它们有一个功能
REGEXP_REPLACE(col, regexp, replace)
参见MariaDB文档和PCRE正则表达式增强
注意，您也可以使用regexp分组(我发现这非常有用):
SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')
返回
over - stack - flow
