Postgresql REGEXP_REPLACE Function

In this PostgreSQL tutorial, we will learn about “Postgresql regexp_replace” and cover the following topics.

• Postgresql regexp_replace special characters
• Postgresql regexp_replace multiple
• Postgresql regexp_replace flags
• Postgresql regexp_replace does not exist
• Postgresql regexp_replace remove spaces
• Postgresql regexp_replace characters
• Postgresql regexp_replace space
• Postgresql regexp_replace non alphanumeric
• Postgresql regexp_replace all occurrences

Postgresql regexp_replace

In Postgresql, the REGEXP_REPLACE function replaces substrings that match a POSIX regular expression with a new substring.

The syntax of regexp_replace is given below.

REGEXP_REPLACE(source, pattern, replacement)

Where the source is a string where the replacement will take.

pattern: The pattern that we search in the source.

replacement: It is a string that replaces the substrings which match the regular expression pattern.

Let’s understand with an example.

SELECT REGEXP_REPLACE('United_States','_',' ');

Here in the above output, we replace the underscore between the word United and States with a space.

As we can see in the above output, we have replaced the underscore with a space.

Postgresql regexp_replace special characters

In Postgresql, the regexp_replace function can replace the special characters in any string.

Let’s understand through an example.

SELECT regexp_replace('jhon@gmail.com','[^\w]+','');

In the above code, the source is ‘jhon@gmail.com’ with the special character @, the pattern is ‘[^\w]+’, which means replacing everything that is not number, digit, underline with the nothing.

The output of the above code is given below.

In the above output, the email ‘jhon@gmail.com‘ contains a special character @, this special character replaced with nothing ”.

Also, read: PostgreSQL Loop Examples

Postgresql regexp_replace remove spaces

In Postgresql, the regexp_replace function can also replace the spaces from the string.

Let’s join the first and last name of the person by removing space.

SELECT regexp_replace('Danny Ron',' ','');

In the above code, the source is ‘Danny Ron’ with space-separated first and last name, the pattern is space that is represented using the two single quotes with space in between. the replacement string is also two single quotes that represent nothing.

The output of the above code is given below.

Read: How to create a table in PostgreSQL

Postgresql regexp_replace characters

In Postgresql, the regexp_replace function can replace the character from any string with any character.

Let’s replace the character ‘e’ to ‘i’ in of There using the below code.

SELECT regexp_replace('There','e','i');

In the above code, the source is the word There, and the pattern is the character ‘e’ that we want to replace. The replacement string or new string is character ‘i’.

We replacing the character ‘e’ in the word ‘There’ with ‘i’.

The output of the above code is given below.

In the above output, the regexp_replace function replaced the first occurrence of character e in the word ‘There’ with ‘i’. But it didn’t replace the second occurrence of the character e.

Don’t worry in the sub-section “Postgresql regexp_replace all occurrences”, we will learn about How to replace all occurrences of character or string.

Read: PostgreSQL DATE Functions

Postgresql regexp_replace space

In Postgresql, we can also replace the space with any character or symbol with the help of regexp_replace.

Let’s replace the space with a hyphen (- ).

SELECT regexp_replace('Thomas D',' ','-');

In the above code, the source is named ‘Thomas D’ with some space, the pattern is space that is represented using the two single quotes with space in between. the replacement string is a hyphen (-) symbol.

we are replacing the space with a hyphen (-).

The output of the above code is given below.

Read: PostgreSQL ADD COLUMN

Postgresql regexp_replace non alphanumeric

In Postgresql, the regexp_replace can remove or replace the non-alphanumeric, non-alphanumeric is all the characters except alphabets and numbers.

Let’s remove the non-alphanumeric from the string.

SELECT regexp_replace('some12-34@#$', '\W+', '', 'g');

In the above code, the source is ‘some12-34@#$’, and the pattern is ‘\W+’ that represents all the characters except alphabets and numbers, the replacement string is a double single quote that represents nothing.

The ‘g’ is used to tell that apply this operation to all non-alphanumeric.

The output of the above code is given below.

In the above code, we have removed all characters except alphabets and numbers.

Read: PostgreSQL WHERE IN 

Postgresql regexp_replace all occurrences

In Postgresql, the regexp_replace can replace all occurrences of a specific character or word. This means if you have a sentence that contains lots of a character named ‘f’, then we can replace all the ‘f’ characters in a sentence.

Let’s understand through an example.

SELECT regexp_replace('Hello, I am Denny and I live in den', 'e', 'ai', 'g');

In the above code, the regexp_replace function replaces the all ‘e’ character in a sentence (“Hello, I am Denny and I live in den”) with a character ‘ai’, where the pattern is the ‘e’ and replacement string is the ‘ai’.

The flag ‘g’ is used to indicate that replace all the occurrences of ‘e’ in a sentence.

The output of the above code is given below.

Also, check the following tutorials.

• PostgreSQL WHERE
• PostgreSQL CASE
• Postgres RegEx
• Postgresql while loop
• Postgresql cast int
• Postgresql replace
• Postgresql Delete Row
• PostgreSQL DATE_PART()

So, in this tutorial, we have learned about “Postgresql regexp_replace” and covered the following topics.

• Postgresql regexp_replace special characters
• Postgresql regexp_replace multiple
• Postgresql regexp_replace flags
• Postgresql regexp_replace does not exist
• Postgresql regexp_replace remove spaces
• Postgresql regexp_replace characters
• Postgresql regexp_replace space
• Postgresql regexp_replace non alphanumeric
• Postgresql regexp_replace all occurrences