REGEXP_REPLACE()

Function Description

REGEXP_REPLACE() is used to replace the string matching the given regular expression pattern with the specified new string.

grammar

> REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Parameter definition

  • expr is the string to be replaced.

  • pat This is a regular expression, and the function looks for all strings that match this pattern.

  • repl is a replacement string that replaces the matching string found.

  • pos: This is an optional parameter that specifies where to start the search. The default value is 1, indicating that the search starts from the start position of the string.

  • occurrence: This is an optional parameter that specifies the position to replace the match. The default value is 1, indicating that the first match is returned.

  • The match_type parameter is an optional string that specifies the way to match. This parameter can be composed of the following characters, each character specifies a matching method, and the order of characters does not affect the result:

    • `’c’: case sensitive for matching (i.e. uppercase and lowercase letters are treated as different characters). By default, matches are case sensitive.

    • 'i': matches insensitively (i.e. uppercase and lowercase letters are treated as the same characters).

    • 'n': Allows the . symbol to match newlines. By default, the . symbol does not match newlines.

    • 'm': Treat a string as multiple lines. That is,^matches the beginning of the string or the beginning of any line, and$matches the end of the string or the end of any line. By default,^only matches the beginning of the string, and$` only matches the end of the string.

    • `’u’: Treats the pattern as a UTF-8 string. By default, the pattern is treated as a byte string.

Example

mysql> SELECT REGEXP_REPLACE('Hello, World!', 'World', 'Universe');
+-----------------------------------------------------------+
| regexp_replace(Hello, World!, World, Universe) |
+-----------------------------------------------------------+
| Hello, Universe! |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_REPLACE('Cat Dog Cat Dog Cat','Cat', 'Tiger') 'Result';
+--------------------------------+
| Results |
+--------------------------------+
| Tiger Dog Tiger Dog Tiger |
+--------------------------------+
1 row in set (0.01 sec)