In PostgreSQL, we can use the regexp_replace() function to replace a substring within a given string, based on a given POSIX regular expression. We can specify that all matches are replaced or just the first match.

We pass the string as the first argument, the pattern as the second, and the replacement text as the third argument. We also have the option of specifying the start position as the fourth argument, and we can specify a flag to determine how the function behaves.

Example

Here’s a basic example to demonstrate:

SELECT regexp_replace('cat cot', 'c.t', 'dog');

Result:

dog cot

By default, only the first matching substring is replaced.

Specifying a Starting Position

We can specify a starting position by providing a fourth argument:

SELECT regexp_replace('cat cot', 'c.t', 'dog', 4);

Result:

cat dog

In this case I specified 4 as the fourth argument, and so the function started from the fourth character.

Replace All Matches

We can add the g flag in order to replace all matching substrings:

SELECT regexp_replace('cat cot', 'c.t', 'dog', 'g');

Result:

dog dog

Here’s another example:

SELECT regexp_replace('cat cot', '(c.t)\s(c.t)', 'dog', 'g');

Result:

dog

Replace the Nth Match

We can specify which match to replace by following the starting position up with another integer:

SELECT regexp_replace('cat cot cite cut', 'c.t', 'dog', 1, 3);

Result:

cat cot doge cut

In this case I started the search at position 1 and replaced the 3rd match.

If we specify zero, then all matches are replaced:

SELECT regexp_replace('cat cot cite cut', 'c.t', 'dog', 1, 0);

Result:

dog dog doge dog

No Match

If there are no matches, the original string is returned:

SELECT regexp_replace('cat cot', 'd.w', 'dog', 'g');

Result:

cat cot

Other Flags

We can also add other flags (in addition to the g flag) to change the behaviour of the function. The following table contains the other flags we can use:

Option	Description
b	Rest of RE is a BRE
c	Case-sensitive matching (overrides operator type)
e	Rest of RE is an ERE
i	Case-insensitive matching (overrides operator type)
m	Historical synonym for n
n	Newline-sensitive matching
p	Partial newline-sensitive matching
q	Rest of RE is a literal (“quoted”) string, all ordinary characters
s	Non-newline-sensitive matching (default)
t	Tight syntax (default)
w	Inverse partial newline-sensitive matching
x	Expanded syntax

Source: https://www.postgresql.org/docs/current/functions-matching.html#POSIX-METASYNTAX

Here’s an example of using the i flag to specify a case insensitive search:

SELECT 
    regexp_replace('cat cot', 'C.T', 'dog', 'g') AS "Case Sensitive",
    regexp_replace('cat cot', 'C.T', 'dog', 'gi') AS "Case Insensitive";

Result:

 Case Sensitive | Case Insensitive 
----------------+------------------
 cat cot        | dog dog

More About POSIX Regular Expressions in PostgreSQL

See the PostgreSQL documentation for an overview of how POSIX regular expressions work and the various options available when using the regex_replace() function.