MySQL REGEXP_REPLACE() Function

The MySQL REGEXP_REPLACE() function is used for pattern matching. This function searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified string that matches the given regular expression pattern. If the match is found, it returns the whole string along with the replacements. If there is no match is found, the returned string is unchanged. If the expression, patterns, and replacement string are NULL, the function will return NULL.

REGEXP_REPLACE(), REPLACE(), and the TRANSLATE() function in MySQL are works as same, except that the TRANSLATE allows us to make multiple single-character substitutions and the REPLACE function replaces one whole string with another string, while REGEXP_REPLACE search a string for a regular expression pattern.

Syntax

The following is a basic syntax to use this function in MySQL:

REGEXP_REPLACE (expression, pattern, replace_string[, pos[, occurrence[, match_type]]])

Parameter Explanation

The explanation of the REGEXP_REPLACE() function parameters are:

expression: It is an input string on which we will perform searching through regular expressions parameters and functions.

patterns: It represents the regular expression pattern for a substring.

replace_string: It is a substring that will be substituted if the match is found.

The REGEXP_INSTR() function uses various optional parameters that are given below:

pos: It is used to specify the position in expression within the string to start the search. If we do not specify this parameter, it will start at position 1.

occurrence: It is used to specify for which occurrence of a match we are going to search. If we do not specify this parameter, all occurrences are replaced.

match_type: It is a string that allows us to refine the regular expression. It uses the following possible characters to perform matching.

• c: It represents a case-sensitive matching.
• i: It represents a case-insensitive matching.
• m: It represents a multiple-line mode that allows line terminators within the string. By default, this function matches line terminators at the start and end of the string.
• n: It is used to modify the . (dot) character to match line terminators.
• u: It represents Unix-only line endings.

Let us understand it with various examples.

Example

The following statement explains the basic example of the REGEXP_REPLACE function in MySQL.

mysql> SET @str = 'TheDeveloperBlog is a great websites';

mysql> SELECT @str AS 'Original String', 
REGEXP_REPLACE(@str, 'great', 'an educational') AS 'Replaceable String';

Here is the output:

Suppose our input string contains multiple matches within the string, then this function will replace all of them. See the below statements:

mysql> SET @str = 'BCA MCA BBA BCA BBA';
mysql> SELECT @str AS 'Original String',
REGEXP_REPLACE(@str, 'BBA', 'PGDCA') AS 'Replaceable String';

Here is the output:

If the input string and replaceable string have no matching, the statements return the original string. See the below example:

mysql> SET @str = 'BCA MCA BBA BCA BBA';
mysql> SELECT @str AS 'Original String',
REGEXP_REPLACE(@str, 'MBA', 'PGDCA') AS 'Resultant String';

Here is the output:

If we want to replace the string by specifying the position to start replacement, we can use the REGEX_REPLACE function as follows:

mysql> SET @str = 'BBA BCA BBA BCA BBA';
mysql> SELECT @str AS 'Original String',
REGEXP_REPLACE(@str, 'BBA', 'MCA', 2) AS 'Replaceable String';

In this statement, we have specified the position as 2 to start the replacement. Executing this query, we will get the below output where we can see that the first position of the replaceable string is not replaced.

We know that all occurrences of the matching string are replaced by default. However, we also have an option to specify the specific occurrence for replacing the matching string by using the occurrence parameter. See the below example:

mysql> SET @str = 'BBA BCA BBA BCA BBA';
mysql> SELECT @str AS 'Original String',
REGEXP_REPLACE(@str, 'BBA', 'MCA', 2, 2) AS 'Replaceable String';

In this example, the starting position of the replaceable string is 2 that came after the first occurrence had started. Therefore, occurrence 2 became occurrence 1, and occurrence 3 became occurrence 2. See the below output:

We can provide an additional parameter to refine the regular expression by using the match type arguments. For example, we can use it to verify whether the match is case-sensitive or include line terminators. See the below example where we are specifying a case-sensitive and case-insensitive match:

mysql> SET @str = 'BBA BCA BBA BCA BBA';

mysql> SELECT @str AS 'Original String',
REGEXP_REPLACE(@str, 'bba', 'MCA', 1, 0, 'c') AS ' Case-Sensitive',
REGEXP_REPLACE(@str, 'bba', 'MCA', 1, 0, 'i') AS ' Case-Insensitive';

Here is the output: