MySQL | REGEXP_REPLACE method
Start your free 7-days trial now!
MySQL's REGEXP_REPLACE(~) method returns the input string expr with all occurrences that match the regular expression pat replaced by the new substring repl.
Parameters
1. expr | string
The string to perform regular expression matching and replace on.
2. pat | regular expression
The regular expression pattern to be used for matching.
3. repl | string
The substring to replace the occurrences in expr that match the regular expression pattern.
4. pos | integer | optional
The position in expr at which to start the search. Defaults to 1.
5. occurrence | number | optional
Which occurrence of a match to replace. Defaults to 0 (all occurrences).
6. match_type | string | optional
Specifies how to perform matching. Multiple characters may be specified, however, if there is a contradiction between the provided match_types the match_type on the right take precedence.
match_type | Meaning |
|---|---|
| Case sensitive matching. |
| Case-insensitive matching. |
| 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. |
| The |
| Unix-only line endings. Only the newline character is recognized as a line ending by the |
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above table can be created using the code here.
Basic usage
To replace all occurrences of [ae] regular expression match with '%' in student last names:
SELECT lname, REGEXP_REPLACE(lname, '[ae]', '%')FROM students;
+--------+----------------------------------+| lname | REGEXP_REPLACE(lname,'[ae]','%') |+--------+----------------------------------+| Towner | Town%r || Davis | D%vis || Apple | %ppl% || David | D%vid || Town | Town |+--------+----------------------------------+
Notice how all a and e characters in student last names have now been replaced with %.
Position
To only start the regular expression matching from position 4:
SELECT REGEXP_REPLACE('abcdefghi', '[a-z]', 'X', 4, 0, 'c');
+------------------------------------------------------+| REGEXP_REPLACE('abcdefghi', '[a-z]', 'X', 4, 0, 'c') |+------------------------------------------------------+| abcXXXXXX |+------------------------------------------------------+
Notice how we only start replacing from position 4 (occupied by d in the original string 'abcdefghi').
Occurence
To replace the second occurrence of match:
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]', 'X', 1, 2, 'c');
+--------------------------------------------------------+| REGEXP_REPLACE('abc def ghi', '[a-z]', 'X', 1, 2, 'c') |+--------------------------------------------------------+| aXc def ghi |+--------------------------------------------------------+
Notice how we only replace the second occurrence of regular expression match ('b' in the original string 'abc def ghi').
Match type
To perform a case sensitive match:
SELECT REGEXP_REPLACE('abc DEF ghi', '[a-z]+', 'X', 1, 0, 'c');
+---------------------------------------------------------+| REGEXP_REPLACE('abc DEF ghi', '[a-z]+', 'X', 1, 0, 'c') |+---------------------------------------------------------+| X DEF X |+---------------------------------------------------------+
Notice how 'DEF' is not replaced with 'X' as we specified match_type of 'c'. If we had specified a match_type of 'i' (case insensitive) we would have returned 'X X X'.
