Custom regex function to replace characters in MySQL

Soumitra

This tutorial shows how to replace the characters in a string or text using regular expression in MySQL function. There is no built-in function available to replace any character in a string or text in MySQL so here I am creating a custom function.
The below function takes three arguments

pattern – the regular expression which will be search in the content
replacement – the replacement text which will be used to replace the pattern
original – the original content or string in which the pattern is searched and replaced

DELIMITER $$
USE `somedb`$$
DROP FUNCTION IF EXISTS `regex_replace`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `regex_replace`(pattern VARCHAR(1000),
replacement VARCHAR(1000),original VARCHAR(1000)) RETURNS VARCHAR(1000) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE temp VARCHAR(1000);
     DECLARE ch VARCHAR(1);
     DECLARE i INT;
     DECLARE j INT;
     DECLARE qbTemp VARCHAR(1000);
     SET i = 1;
     SET j = 1;
     SET temp = '';
     SET qbTemp = '';
    
     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;
     SET temp = TRIM(BOTH replacement FROM temp);
     SET temp = REPLACE(REPLACE(REPLACE(temp , CONCAT(replacement,replacement),
CONCAT(replacement,'#')),CONCAT('#',replacement),''),'#','');
     RETURN temp;
    END$$
DELIMITER ;

 

Usage:

SELECT regex_replace('-',' ','how-to-create-a-custom-mysql-function-to-replace-char-using-regex')

 

output

how to create a custom mysql function to replace char using regex

 

That’s all. Thank you for your patience. Please do not forget to leave a comment.

3 Thoughts to “Custom regex function to replace characters in MySQL”

  1. Tim HJ
    15th Mar, 2016 at 9:15 am

    MySQL’s replace function already does what your function does, what you’ve written is a program that takes more resource and time to do than the native mysql function.

    select replace(‘how-to-create-a-custom-mysql-function-to-replace-char-using-regex’,’-‘,’ ‘);
    returns
    > how to create a custom mysql function to replace char using regex

    Reply
  2. Allan
    29th Sep, 2015 at 7:29 pm

    Doesn’t seem to be working for me

    SELECT
    REGEX_REPLACE(‘[1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9]’,
    ‘*********’,
    sql_log)
    FROM
    somedb.sometable
    WHERE
    log_event_id = ‘1873107’

    Should return ********* in space of the 9 digit number

    Reply
    1. Soumitra
      30th Sep, 2015 at 2:24 am

      it is not so rigorous function to meet your needs. you can create your own function. It takes only one character for pattern searching and replace that character by the replaceable pattern.

      Reply

Leave a Comment