REGEXP_REPLACE
Replaces the substrings that match a regular expression with the given string.
PostgreSQL 17.9
REGEXP_REPLACE(source, pattern, replacement[, start[, occurrence]][, flags])- [ ]optional part
- source
- Source stringtext
- pattern
- Regular expressiontext
- replacement
- String inserted instead of the matchtext
- start
- Position to start the search from, 1 by defaultintegerOptional
- occurrence
- Number of the match to replace, 0 for all matches, the first one by defaultintegerOptional
- flags
- Flags, for example 'gi' for all matches ignoring casetextOptionalbcegimnpqstwx
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.9
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.9
SELECT REGEXP_REPLACE('a1b22c333', '[0-9]+', '*', 1, 2);Reordering date parts
PostgreSQL 17.9
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.9
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.