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.
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
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
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.