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.