PostgreSQL – REGEXP_REPLACE Function

The PostgreSQL REGEXP_REPLACE() function is used to replaces substrings that match a POSIX regular expression with a new substring.

Syntax: REGEXP_REPLACE(source, pattern, replacement_string, [, flags])

Let’s analyze the above syntax:

• The source is a string where the search and replace operation in executed.
• The pattern is a POSIX regular expression for matching substrings which is to be replaced.
• The replacement_string is a string which replaces the substrings using match the regular expression pattern.
• The flags argument is used to control the behaviour of the function for matching characters.
• The PostgreSQL REGEXP_REPLACE() function returns the final string after the replacement of the original string with the substring.

Example 1:

For instance imagine you have a name of a person in the following format:

first_name last_name

And you want to rearrange the name as follows:

last_name, first_name

To do this, you can use the REGEXP_REPLACE() function as shown below:

SELECT REGEXP_REPLACE('Raju Kumar', '(.*) (.*)', '\2, \1');

Output:

Example 2:

Suppose you have data in the form of a string. This string is mixed with alphabets and digits as follows:

ABC12345xyz

The following query removes all alphabets e.g., A, B, C, etc from the source string:

SELECT REGEXP_REPLACE('ABC12345xyz', '[[:alpha:]]', '', 'g');

Output: