PostgreSQL 18 reference · String Functions

PostgreSQL REGEXP_REPLACE() Function

Replace text matching a POSIX regular expression. Add the 'g' flag to replace every match, not just the first.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

regexp_replace(string, pattern, replacement, flags)

Parameter	Description
`string`	The input text.
`pattern`	A POSIX regular expression.
`replacement`	The new text; `\1` refers to a captured group.
`flags` optional	`g` all matches, `i` ignore case.

Text.

First match or all

SELECT regexp_replace('a1b22c333', '\d+', '#') AS first_only,
       regexp_replace('a1b22c333', '\d+', '#', 'g') AS all_matches;

Output

 first_only | all_matches
------------+-------------
 a#b22c333  | a#b#c#
(1 row)

Run this on your computer — see “Get Started” for setup.

Reorder with groups

SELECT regexp_replace('2026-03-09', '(\d+)-(\d+)-(\d+)', '\3/\2/\1') AS uk_date;

Output

  uk_date
------------
 09/03/2026
(1 row)

Run this on your computer — see “Get Started” for setup.

REPLACE() · Replace every occurrence of a substring with another string. The search is case-sensitive.
REGEXP_MATCH() · Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.
TRANSLATE() · Replace single characters by position: each character in 'from' becomes the matching character in 'to', or is deleted.
SQL String Functions · tutorial