mysql5.7自定义函数实现regex_replace正则替换功能

mysql 8.0以上的版本才支持 regex_replace 函数，5.7版本要实现正则替换只能自己写函数实现。

准备工作

1. 查看mysql当前是否支持编写自定义函数

show variables like '%fun%';

结果为OFF则为未开启

2. 未开启自定义函数的话需要开启

set global log_bin_trust_function_creators=1; 

编写自定义函数

drop function if exists GET_LEFT_DELIMITER;
DELIMITER //
CREATE FUNCTION GET_LEFT_DELIMITER(s VARCHAR(16383), p VARCHAR(16383))
RETURNS VARCHAR(16383) DETERMINISTIC
BEGIN
   DECLARE position INT DEFAULT 0;
   SET position = CHAR_LENGTH(s);
   SET p        = CONCAT('^(', p, ')$');

   WHILE position && !(SUBSTR(s, 1, position) REGEXP p) DO
      SET position = position - 1;
   END WHILE;
   RETURN SUBSTR(s, 1, position);
END//
DELIMITER ;

drop function if exists GET_RIGHT_DELIMITER;
DELIMITER //
CREATE FUNCTION GET_RIGHT_DELIMITER(s VARCHAR(16383), p VARCHAR(16383))
RETURNS VARCHAR(16383) DETERMINISTIC
BEGIN
   DECLARE position INT DEFAULT 1;
   SET p = CONCAT('^(', p, ')$');

   WHILE position<=CHAR_LENGTH(s) && !(SUBSTR(s, position) REGEXP p) DO
      SET position = position + 1;
   END WHILE;
   RETURN SUBSTR(s, position);
END//
DELIMITER ;

drop function if exists GET_LEFT_CHUNK;
DELIMITER //
CREATE FUNCTION GET_LEFT_CHUNK(s VARCHAR(16383), p VARCHAR(16383))
RETURNS VARCHAR(16383) DETERMINISTIC
BEGIN
   DECLARE position INT DEFAULT 1;

   IF CHAR_LENGTH(GET_LEFT_DELIMITER(s, p)) THEN
      RETURN '';
   END IF;
   WHILE position<=CHAR_LENGTH(s) && !(SUBSTR(s, 1, position) REGEXP p) DO
      SET position = position + 1;
   END WHILE;
   SET position = position - CHAR_LENGTH(GET_RIGHT_DELIMITER(SUBSTR(s, 1, position), p));
   RETURN SUBSTR(s, 1, position);
END//
DELIMITER ;

drop function if exists REGEXP_SPLIT;
DELIMITER //
CREATE FUNCTION REGEXP_SPLIT(s VARCHAR(16383), p VARCHAR(16383),j VARCHAR(16383), d TINYINT)
RETURNS VARCHAR(16383) DETERMINISTIC
BEGIN
   DECLARE delim    VARCHAR(16383) DEFAULT '';
   DECLARE chunk    VARCHAR(16383) DEFAULT '';
   DECLARE result   VARCHAR(16383) DEFAULT '';

   IF LOCATE(j, s) THEN
      SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = 'Input string must not contain joining delimiter';
   END IF;

   IF CHAR_LENGTH(GET_LEFT_DELIMITER(s, p)) THEN
      SET result = CONCAT(result, j, '');
   END IF;
   WHILE(CHAR_LENGTH(s)) DO
      SET delim = GET_LEFT_DELIMITER(s, p);
      IF d THEN
         SET result = CONCAT(result, j, delim);
      END IF;
      SET s     = SUBSTR(s, 1 + CHAR_LENGTH(delim));
      SET chunk = GET_LEFT_CHUNK(s, p);
      SET result= CONCAT(result, j, chunk);
      SET s     = SUBSTR(s, 1 + CHAR_LENGTH(chunk));
   END WHILE;
   RETURN SUBSTR(result, 1 + CHAR_LENGTH(j));
END//
DELIMITER ;

drop function if exists REGEXP_REPLACE;
DELIMITER //
CREATE FUNCTION REGEXP_REPLACE(s VARCHAR(16383), p VARCHAR(16383),r VARCHAR(16383))
RETURNS VARCHAR(16383) DETERMINISTIC
BEGIN
   DECLARE j      VARCHAR(16383) DEFAULT '!@#$%^&*)(*&^%$#@!';
   RETURN REPLACE(REGEXP_SPLIT(s, p, j, 0), j, r);
END//
DELIMITER ;

测试效果

select regexp_replace('hello world','ll','') from dual;

参考资料

https://blog.csdn.net/qq_35861801/article/details/103183360
https://github.com/almadomundo/mysql-regexp