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: