Summary: in this tutorial, you will learn how to use the PostgreSQL REGEXP_REPLACE() function to replace strings that match a regular expression.
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]) Code language: CSS (css)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'); Code language: JavaScript (javascript)The output is of the statement is:
'Doe, John'Code language: JavaScript (javascript)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');Code language: JavaScript (javascript)The output is:
'12345'Code language: JavaScript (javascript)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');Code language: JavaScript (javascript)And the output is:
'ABCxyz'Code language: JavaScript (javascript)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,}',' ');Code language: JavaScript (javascript)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.
