Skip to content
Related Articles

Related Articles

PostgreSQL – REGEXP_REPLACE Function
  • Last Updated : 01 Feb, 2021

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.

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :