regexp_replace()

A function for replacing values in a string using a regular expression

regexp_replace() is a system function for replacing values in a string which match a POSIX regular expression.

regexp_replace() was added in PostgreSQL 8.1.

Usage

regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text

PostgreSQL 15 and later:

regexp_replace ( string text, pattern text, replacement text [, start integer ] [, flags text ] ) → text
regexp_replace ( string text, pattern text, replacement text, start integer, N integer [, flags text ] ) → text

A list of flags which can be used with regexp_replace() is available in the PostgreSQL documentation: ARE Embedded-Option Letters.

Change history

Examples

Basic execution example for regexp_replace():

postgres=# SELECT regexp_replace('foobar', 'f\w\w', 'moo');
 regexp_replace 
----------------
 moobar
(1 row)

Using the 'g' ("global") flag to replace multiple occurrences of a pattern:

postgres=# SELECT regexp_replace('foobarboo flooobiloo', 'o{2,}', 'uu', 'g');
   regexp_replace    
---------------------
 fuubarbuu fluubiluu
(1 row)

In PostgreSQL 15 and later, the starting position can be optionally specified:

postgres=# SELECT regexp_replace('foobarboo flooobiloo', 'o{2,}', 'uu', 3);
    regexp_replace    
----------------------
 foobarbuu flooobiloo
(1 row)

To replace all occurrences from the specified starting position, use:

postgres=# SELECT regexp_replace('foobarboo flooobiloo', 'o{2,}', 'uu', 3, 0, 'g');
   regexp_replace    
---------------------
 foobarbuu fluubiluu
(1 row)

Categories

String manipulation, System function

See also

replace(), translate(), regexp_match(), regexp_matches()