REGEXP_REPLACE
Replaces the matches of a regular expression with another string.
MySQL 8.4
REGEXP_REPLACE(expr, pattern, replacement[, pos[, occurrence[, match_type]]])- [ ]optional part
- expr
- Source stringVARCHAR
- pattern
- Regular expressionVARCHAR
- replacement
- String inserted instead of the matchVARCHAR
- pos
- Position to start the search from, from 1 to the string length + 1, 1 by defaultINTOptional
- occurrence
- Number of the match to replace, 0 (all matches) by defaultINTOptional
- match_type
- Match flags, can be combinedVARCHAROptionalcimnu
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.4
SELECT REGEXP_REPLACE('sql-academy-2024', '[0-9]', '*');Replacing only the second number
MySQL 8.4
SELECT REGEXP_REPLACE('a1b22c333', '[0-9]+', '*', 1, 2);Reordering date parts
MySQL 8.4
SELECT REGEXP_REPLACE(
'2024-03-08',
'([0-9]{4})-([0-9]{2})-([0-9]{2})',
'$3.$2.$1'
);Phone digits only
MySQL 8.4
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.