PostgreSQL Tutorial: REGEXP_REPLACE Function

Summary: In this tutorial, you will learn how to use the PostgreSQL REGEXP_REPLACE() function to replace strings that match a regular expression.

Table of Contents

The PostgreSQL REGEXP_REPLACE() function replaces substrings that match a POSIX regular expression by a new substring.

Note that if you want to perform simple string replacement, you can use the REPLACE() function.

Syntax

The syntax of the PostgreSQL REGEXP_REPLACE() function is as follows:

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

Arguments

The REGEXP_REPLACE() function accepts four arguments:

1) source

The source is a string that replacement should be taken place.

2) pattern

The pattern is a POSIX regular expression for matching substrings that should be replaced.

3) replacement_string

The replacement_string is a string that to replace the substrings which match the regular expression pattern.

4) flags

The flags argument is one or more character that controls the matching behavior of the function e.g., i allows case-insensitive matching, n enables matching any character and also the newline character.

Return value

The PostgreSQL REGEXP_REPLACE() function returns a new string with the substrings, which match a regular expression pattern, replaced by a new substring.

Examples

Let’s see some examples to understand how the REGEXP_REPLACE() function works.

A) Name rearrangement

Suppose, you have a name of a person in the following format:

first_name last_name

For example, John Doe

And you want to rearrange the this name as follows for the reporting purpose.

last_name, first_name

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

SELECT REGEXP_REPLACE('John Doe','(.*) (.*)','\2, \1');

The output of the statement is:

'Doe, John'

B) String removal

Imagine you have string data with mixed alphabets and digits as follows:

ABC12345xyz

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

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

The output is:

'12345'

In this example,

• [[:alpha:]] matches any alphabets
• '' is the replacement string
• 'g' instructs the function to remove all alphabets, not just the first one.

Similarly, you can remove all digits in the source string by using the following statement:

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

And the output is:

'ABCxyz'

C) Redundant spaces removal

The following example removes unwanted spaces that appear more than once in a string.

SELECT REGEXP_REPLACE('This  is    a   test   string','( ){2,}',' ');

The following picture illustrates the output:

In this tutorial, you have learned how to use the PostgreSQL REGEXP_REPLACE() function to replace substrings that match a regular expression with a new substring.

See more

PostgreSQL Tutorial: String Functions

PostgreSQL Documentation: String Functions and Operators