REGEXP_REPLACE
Replaces the substrings that match a regular expression with the given string.
PostgreSQL 17.5
REGEXP_REPLACE(source, pattern, replacement[, start[, occurrence]][, flags])Parameters
- sourcetext
- Source string
- patterntext
- Regular expression
- replacementtext
- String inserted instead of the match
- startintegeroptional
- Position to start the search from, 1 by default
- occurrenceintegeroptional
- Number of the match to replace, 0 for all matches, the first one by default
- flagstextoptional
- Flags: g replaces all matches, i ignores case
Return value
A string with the replacement; without the g flag and the occurrence argument only the first match is replaced
Examples
Replacing with and without the g flag
PostgreSQL 17.5
SELECT REGEXP_REPLACE('sql-academy-2024', '[0-9]', '*') AS first_only,
REGEXP_REPLACE('sql-academy-2024', '[0-9]', '*', 'g') AS all_matches;Replacing only the second number
PostgreSQL 17.5
SELECT REGEXP_REPLACE('a1b22c333', '[0-9]+', '*', 1, 2);Reordering date parts
PostgreSQL 17.5
SELECT REGEXP_REPLACE(
'2024-03-08',
'([0-9]{4})-([0-9]{2})-([0-9]{2})',
'\3.\2.\1'
);Spaces in names to underscores
PostgreSQL 17.5
SELECT good_name,
REGEXP_REPLACE(good_name, ' ', '_', 'g') AS slug
FROM Goods
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 \& is the whole match. Without the g flag only the first match is replaced. In MySQL all matches are replaced by default, and a group in the replacement is written as $1.