侧边栏壁纸
博主头像
luoxx博主等级

只要思想不滑坡,办法总比困难多

  • 累计撰写 31 篇文章
  • 累计创建 44 个标签
  • 累计收到 156 条评论

目 录CONTENT

文章目录

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

luoxx
2022-05-22 / 0 评论 / 0 点赞 / 721 阅读 / 550 字
温馨提示:
本文最后更新于 2022-09-14,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

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

准备工作

  1. 查看mysql当前是否支持编写自定义函数
show variables like '%fun%';

结果为OFF则为未开启

  1. 未开启自定义函数的话需要开启
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;

iShot_2022-05-22_21.50.30

参考资料

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

0

评论区