MySQL regexp_replace() Function
Returns occurrences in the string expr that match the pattern pat with the replacement repl, and returns the resulting string. If any of expr, pat, or repl are NULL, then the function will return NULL. The basic syntax is
REGEXP_REPLACE(expr, pat, repl [, pos[, occurrence[,match_type]]]);
The Optional parameters allowed are :
- pos: the position in expr at which to start. Default is 1.
- occurrence: which occurrence to replace. Default is 0, which means to replace all occurrences.
- match_type: a string specifying how to perform the match. It uses the following possible characters to perform matching.
- c: case-sensitive matching.
- i: case-insensitive matching.
- m: a multiple-line mode that recognizes 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. By default, it will stop at the end of a line.
- u: It represents Unix-only line endings that recognize only the newline character by the ., ^, and $ match operators.
Examples:
1. Basic usage
In this example, we replace the word be with code everywhere it occurs in the string.
select REGEXP_REPLACE('to be, or not to be, that is the question.','be','code');
2. Replace from an offset and the occurrence.
Here we are replacing the alphabets from offset 1 and the 3rd occurrence onwards. So only the last sets of alphabets will be changed. However, in the second part, all the strings will be changed.
SELECT REGEXP_REPLACE ('abc def ghi', '[a-z]+', 'X', 1, 3),REGEXP_REPLACE ('abc def ghi', '[a-z]+', 'X');
3. Replace with Case-sensitive option
We are purposely using the case-sensitive option so that the replaced string is case-sensitive and also the searched String is case-sensitive. For example, in the first part of select if we do not search for ‘ALL’ then nothing will be replaced since we are using the ‘C’ option here.
SELECT
REGEXP_REPLACE('THISISALLCAPS', 'ALL', 'all', 1, 0, 'c'),
REGEXP_REPLACE('THISISALLCAPS', 'caps', 'bats', 1, 0, 'i');
