The PostgreSQLREGEXP_REPLACE() function replaces substrings that match aPOSIX regular expression with a new substring.

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

Syntax

The syntax of the PostgreSQLREGEXP_REPLACE() function is as follows:

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

Arguments

TheREGEXP_REPLACE() function accepts four arguments:

1)source

The source is a string that replacement should take place.

2)pattern

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

3)replacement_string

Thereplacement_string is a string that replaces the substrings that match the regular expression pattern.

4)flags

Theflags argument is one or more characters that control 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 PostgreSQLREGEXP_REPLACE() function returns a new string with the substrings, which match a regular expression pattern, replaced by a new substring.

Examples

Let’s take some examples to understand how theREGEXP_REPLACE() function works.

1) Name rearrangement

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

first_name last_name

For example,John Doe

You want to rearrange this name as follows for reporting purposes.

last_name, first_name

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

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

The output of the statement is:

'Doe, John'

2) 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');

The output is:

'ABCxyz'

3) Redundant space removal

The following example uses theREGEXP_REPLACE() function to remove redundant spaces:

SELECT REGEXP_REPLACE('Your string with   redundant    spaces','\s{2,}',' ','g')AS cleaned_string;

Output:

cleaned_string----------------------------------- Your string with redundant spaces(1 row)

In this example, we use theREGEXP_REPLACE() function to match two or more consecutive spaces and replace them with a single space.

Summary

• Use the PostgreSQLREGEXP_REPLACE() function to replace substrings that match a regular expression with a new substring.

Last updated onJanuary 29, 2024

On this page

• Syntax
• Arguments
• Return value
• Examples
• Summary