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 opertaion 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.
For instance imagine you have a name of a person in the following format:
And you want to rearrange the name as follows:
To do this, you can use the REGEXP_REPLACE() function as shown below:
SELECT REGEXP_REPLACE('Raju Kumar', '(.*) (.*)', '\2, \1');
Suppose you have data in the form of a string. This string is mixed with alphabets and digits as follows:
The following query removes all alphabets e.g., A, B, C, etc from the source string:
SELECT REGEXP_REPLACE('ABC12345xyz', '[[:alpha:]]', '', 'g');