PostgreSQL regexp_replace() Function

The PostgreSQL regexp_replace() function replaces the first matched substring of the given regular expression with the given replacement in a given string; if the g flag used, replaces all matches.

The regexp_replace() function uses POSIX regular expressions.

regexp_replace() Syntax

This is the syntax of the PostgreSQL regexp_replace() function:

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

Parameters

string

Required. The string to replace in.

regex

Required. The regular expression.

replacement

Required. The string to replace with.

flags

Optional. The match mode of the regular expression.

Return value

The PostgreSQL regexp_replace() function returns the string with the matches substring of the specified regular expression replaced by the specified replacement.

regexp_replace() Examples

This example below shows how to use the regexp_replace() function to replace a substring with another text in a string:

SELECT regexp_replace('Abcd abCd aBcd', 'ab.', 'xx');

 regexp_replace
----------------
 Abcd xxd aBcd

You can use the i flag in the parameter flags to to perform a insensitive-case match. for example:

SELECT regexp_replace('Abcd abCd aBcd', 'ab.', 'xx', 'i');

 regexp_replace
----------------
 xxd abCd aBcd

You can use the g flag in the flags parameter to replace all matches of the regular expression. for example:

SELECT regexp_replace('Abcd abCd aBcd', 'ab.', 'xx', 'ig');

 regexp_replace
----------------
 xxd xxd xxd