MySQL REGEXP_REPLACE() Function

In MySQL, the REGEXP_REPLACE() function replaces each substring of the text String that matches the given regular expression with the given replacement.

By default, The REGEXP_REPLACE() function performs case-insensitive matching.

`REGEXP_REPLACE()` Syntax

here is MySQL REGEXP_REPLACE() syntax:

REGEXP_REPLACE(str, regexp, replacement)
REGEXP_REPLACE(str, regexp, replacement, position)
REGEXP_REPLACE(str, regexp, replacement, position, occurrence)
REGEXP_REPLACE(str, regexp, replacement, position, occurrence, mode)

Parameters

str: Required. The string to search and replace in.
regexp: Required. The regular expression to which this string is to be matched.
replacement: Required. The string to replace with.
position: Optional. The starting position to start the search. The default is to start from scratch.
occurrence: Optional. It indicates which occurrence of a match to replace. Defaults to replacing all matches.
mode: Optional. The match mode. It indicates how to perform matching.

mode can be one or more values in the followings:

c: Case-sensitive
i: Case-insensitive
m: Multi-line match pattern
n: The dot . can match end of line
u: Unix line endings only

If there are conflicting options in mode, the rightmost one takes precedence.

Return value

The REGEXP_REPLACE() function replaces each substring of the text String that matches the given regular expression with the given replacement.

if str, or regexp or replacement is NULL, this function will return NULL.

`REGEXP_REPLACE()` Examples

Here are some common Examples of REGEXP_REPLACE().

Example 1

SELECT REGEXP_REPLACE('123 abc 456 def', '\\s+', '-');

+------------------------------------------------+
| REGEXP_REPLACE('123 abc 456 def', '\\s+', '-') |
+------------------------------------------------+
| 123-abc-456-def                                |
+------------------------------------------------+

Here replaced all spaces with -.

Example 2

SELECT REGEXP_REPLACE('123 abc 456 def', '\\d+', 'X', 1, 2);

+------------------------------------------------------+
| REGEXP_REPLACE('123 abc 456 def', '\\d+', 'X', 1, 2) |
+------------------------------------------------------+
| 123 abc X def                                        |
+------------------------------------------------------+

Here replaced the second substring that has only numbers with X.

REGEXP_REPLACE() Syntax