naveen nani

Posted on 11th June 2024|149 views


Postgresql Regex Replace

Difference between regex_replace() and replace()?

1 answers
chris bons

Posted on 11th June 2024| views

  • 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.


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.


Write your answer


Get a Live FREE Demo

  • Explore the trending and niche courses and learning maps
  • Learn about tuition fee, payment plans, and scholarships
  • Get access to webinars and self-paced learning videos
Course *
Email *
Phone Number