REGEXP_REPLACE
Replaces the matches of a regular expression with another string.
MySQL 8.1
REGEXP_REPLACE(expr, pattern, replacement[, pos[, occurrence[, match_type]]])Parameters
- exprVARCHAR
- Source string
- patternVARCHAR
- Regular expression
- replacementVARCHAR
- String inserted instead of the match
- posINToptional
- Position to start the search from, from 1 to the string length + 1, 1 by default
- occurrenceINToptional
- Number of the match to replace, 0 (all matches) by default
- match_typeVARCHARoptional
- Match flags as in REGEXP_LIKE, for example c for case-sensitive matching
Return value
A string with matches replaced; the original string if there are no matches; NULL if expr, pattern or replacement is NULL
Examples
Replacing all digits
MySQL 8.1
SELECT REGEXP_REPLACE('sql-academy-2024', '[0-9]', '*');Replacing only the second number
MySQL 8.1
SELECT REGEXP_REPLACE('a1b22c333', '[0-9]+', '*', 1, 2);Reordering date parts
MySQL 8.1
SELECT REGEXP_REPLACE(
'2024-03-08',
'([0-9]{4})-([0-9]{2})-([0-9]{2})',
'$3.$2.$1'
);Phone digits only
MySQL 8.1
SELECT name,
REGEXP_REPLACE(phone_number, '[^0-9]', '') AS digits
FROM Users
LIMIT 5;Details
In replacement you can insert a part of the match captured in parentheses: $1 is the first group, $2 is the second and so on. pos must be from 1 to the string length + 1: with 0 or a larger value the function gives an error.