REGEXP_REPLACE()

Description

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

Syntax

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

Explanations

• expr is the string to replace.
• pat This is a regular expression, the function will find all strings that match this pattern.
• repl is the replacement string used to replace the found matching string.
• pos: The position in expr at which to start the search. If omitted, the default is 1.
• occurrence: Which occurrence of a match to replace. If omitted, the default is 0 (which means replace all occurrences).

• match_type: The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:

• 'c': Case-sensitive matching by default.

• 'i': Case-insensitive matching.
• 'n': The . character matches line terminators. The default is for . matching to stop at the end of a line.
• 'm': Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
• 'u': Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

Examples

1. mysql> SELECT REGEXP_REPLACE('Hello, World!', 'World', 'Universe');
2. +------------------------------------------------+
3. | regexp_replace(Hello, World!, World, Universe) |
4. +------------------------------------------------+
5. | Hello, Universe!                               |
6. +------------------------------------------------+
7. 1 row in set (0.00 sec)

9. mysql> SELECT REGEXP_REPLACE('Cat Dog Cat Dog Cat','Cat', 'Tiger') 'Result';
10. +---------------------------+
11. | Result                    |
12. +---------------------------+
13. | Tiger Dog Tiger Dog Tiger |
14. +---------------------------+
15. 1 row in set (0.01 sec)