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

 

Usage:

 

output

 

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

Software Professional, I am passionate to work on web/enterprise application. For more information please go to about me. You can follow on Twitter. You can be a friend on Facebook or Google Plus or Linkedin

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

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

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.