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

 

Usage:

 

output

 

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

Soumitra

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

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

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

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