Custom regex function to replace characters in MySQL

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.