Postgresql Regex Replace

• replace() is used to remove all substrings and replace them with another new_string.

Replace() function used for changing all occurrences in substring with a new string as given in the below example. You can see that.

For example,

SELECT replace('abraka dabra', 'a', 'x');          -- returns "xbrxkx dxbrx"

As mentioned, you can see that it changes all substring with the new string as it is a standard SQL function.

• regex_replace() used to replace only the substring(s) matching a POSIX regular expression.

We can use regex_replace() function for various function works as string removal, extra spaces removal, name rearrangement.

regex_replace() is very useful to manage data properly in PostgreSQL.

Syntax:

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

Source: it is a string on which replacement should take place.

Pattern: an expression that should replace.

Replacement_string used for replacing the substring that matches the POSIX expression pattern.

Flags: controls the matching behaviour of the function.

Ex: ‘g’ is used as a flag not just to remove the first one but removes all alphabets.

       [[: alpha:]] is used to point all the alphabets.

       ‘  ‘  is used as a replacement string.