Regex replace for MySQL > 질문답변

Hi All,

Recently I came across a requirement where I had to cleanse the data that I’m inserting in to MySQL database using regular expressions, so I started searching for a function to do this on MySQL, but unfortunately I couldn’t find any. There was one called UDB but that’s also you need to install a module and stuff like that. I gave up searching finally and wrote my own “regex_replace” MySQL function.



Below is the source code of my function;
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 ;

Note:

If you are using MySQL version 5.0.1 or higher, make sure you set the NO_BACKSLASH_ESCAPES mode ON, before you use the above function to replace any characters which are escaped with back slash “”, ie: A,B,etc… See how to set the NO_BACKSLASH_ESCAPES mode here

Example on how to use this function
 mysql> select regex_replace('[^a-zA-Z0-9-]','','2my test3_text-to. check  my- sql (regular) ,expressions ._,');

Happy Coding!!