Postgres RegEx – Complete tutorial

In this PostgreSQL tutorial, we will learn about PostgreSQL RegEx. RegEx stands for Regular Expressions. RegEx is a sequence of characters that defines a pattern that can be used to filter data in PostgreSQL.

• Postgres Regex
• Postgres regexp_replace
• Postgres regexp_replace All Occurrences
• Postgres regexp_matches in WHERE Clause
• Postgres Regex Substring
• Postgres Regex Numbers Only
• Postgres Regex Split
• Postgres Regex Remove Special Characters

Postgres RegEx

In this section, we will learn about PostgreSQL RegEx. RegEx stands for Regular Expressions. RegEx is a sequence of characters that defines a pattern that can be used to filter data in PostgreSQL.\PostgreSQL uses POSIX or “Portable Operating System Interface for uniX” Regular Expressions which are better than LIKE and SIMILAR TO operators used for pattern matching.

Match Operators in PostgreSQL

OPERATOR	DESCRIPTION
~	This is used to match Regular Expression and it is CASE Sensitive.
~*	This is also used to match Regular Expression and it is CASE Insensitive.
!~	This is used to filter the unmatched Regular Expression and it is CASE Sensitive.
!~*	This is also used to filter the unmatched Regular Expression and it is CASE Insensitive.

Now we will look at some examples where we use these Match Operators in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table as an Email that contains the email of the user who belongs to the United States in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the match operators in the WHERE clause of the SELECT statement in PostgreSQL:

• Match Regular Expression (Case Sensitive)

SELECT *
FROM Email
WHERE Email ~ 'sqlguide';

In this example, we used the match operator ” ~ ” to match the regular expression “sqlguide” with the Email field and we get the results with all the rows having the pattern “sqlguide“.

• Match Regular Expression (Case Insensitive)

SELECT *
FROM Email
WHERE Email ~* 'SQLGUIDE';

In this example, we used the match operator ” ~* ” to match for regular expression “SQLGUIDE” with the Email field we get the results with all the rows having the pattern “SQLGUIDE” with CASE Insensitivity.

• Does not Match Regular Expression (Case Sensitive)

SELECT *
FROM Email
WHERE Email !~ 'sqlguide';

In this example, we used the match operator ” !~ ” to match the regular expression “sqlguide” with the Email field and we get the results with all the rows not having the pattern “sqlguide”.

• Does not Match Regular Expression (Case Insensitive)

SELECT *
FROM Email
WHERE Email !~* 'sqlguide';

In this example, we used the match operator ” ~* ” to match for regular expression “sqlguide” with the Email field we get the results with all the rows not having the pattern “sqlguide” with CASE Insensitivity.

Now we will look at some more examples of PostgreSQL RegEx:

• Matches Beginning of the string

SELECT *
FROM Email
WHERE Email ~ '^j';

In this example, we used the ” ^ ” symbol which denotes the beginning/start of the regex pattern, i.e., if we wish to look for the names that start with the letter ” J ” then we use the “^” symbol.

• Mathes End of the String

SELECT *
FROM Email
WHERE Email ~ 'com$';

In this example, we used the ” $ ” symbol which denotes the end of the regex pattern, i.e., if we wish to look for the Email Addresses that end with ” com ” then we use the “$” symbol.

• Matches Numeric type data

SELECT *
FROM Email
WHERE Email ~ '[0-9]';

In this example, we have displayed those email addresses which contain at least one number using “[0-9]” regular expression.

• Matches Numeric type data (Double Digit)

SELECT *
FROM Email
WHERE Email ~ '[0-9][0-9]';

In this example, we have displayed those email addresses which contain at least two numbers using “[0-9][0-9]” regular expression.

In this section, we learned about PostgreSQL RegEX.

Read: PostgreSQL DATE_PART() Function

Postgres regexp_replace

In this section, we will learn about PostgreSQL REGEXP_REPLACE(). REGEXP_REPLACE is a function in PostgreSQL that is used to replace a sequence of characters using regular expression matching.

The syntax for REGEXP_REPLACE() function in PostgreSQL is:

REGEXP_REPLACE(STRING,PATTERN,REPLACEMENT_STRING,FLAGS)

Now we will look at an example of the REGEXP_REPLACE function in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the REGEXP_REPLACE function to replace the occurrence of a numeric data type with some symbol:

SELECT REGEXP_REPLACE(Email,'[0-9]','*') 
From Email;

It is clear from the screenshot that REGEXP_REPLACE() function replaces the occurrence of any numeric data type of the ” Email ” field with a ” * ” symbol. Here we can note that this function only replaces the first occurrence of the numeric data type.

In this section, we learned about PostgreSQL REGEXP_REPLACE() function.

Read: PostgreSQL DROP TABLE

Postgres regexp_replace All Occurrences

In this section, we will learn about PostgreSQL REGEXP_REPLACE() function to replace all the occurrences of the pattern in the given string. We have to add the flags argument to accomplish this target. We will use the ‘ g ‘ flag which is used to replace all the occurrences of a given pattern.

Now we will look at an example of the REGEXP_REPLACE() function to replace all occurrences of the given pattern in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the REGEXP_REPLACE function to replace all the occurrences of a numeric data type with some symbol:

SELECT REGEXP_REPLACE(Email,'[0-9]','*','g') 
From Email;

It is clear from the screenshot that all the occurrences of the ” Numeric ” type data have been replaced with the ” * ” symbol using the REGEXP_REPLACE( ) function. We passed an argument ‘g’ which searches for all the occurrences of the given pattern.

In this section, we learned about PostgreSQL REGEXP_REPLACE() function to replace all the occurrences of the pattern in the given string.

Read: PostgreSQL INSERT Multiple Rows

Postgres regexp_matches in WHERE Clause

In this section, we will learn about PostgreSQL REGEXP_MATCHES( ) function. REGEXP_MATCHES( ) function is used to match a Regular Expression against a string and it returns the string that matches the pattern in form of a set. We will look at an example of the REGEXP_MATCHES() function.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the PostgreSQL REGEXP_MATCHES( ) function to return a set matching against a regular expression pattern:

SELECT REGEXP_MATCHES(Email,'.+@(.*)$')
FROM Email;

In this example, we used the REGEXP_MATCHES( ) function to search for the domain name of the email address. The REGEXP_MATCHES( ) returned the results in form of a set.

In this section we learned about PostgreSQL REGEXP_MATCHES( ) function.

Read: PostgreSQL ALTER TABLE

Postgres Regex Substring

In this section, we will learn about PostgreSQL RegEx SUBSTRING() function. It is used to extract substrings from a given field using the POSIX Regular expressions. We will look at an example where we will extract some substring from the Email table using the SUBSTRING() function matching against a regular expression.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will see the SUBSTRING() function to extract a substring from the table Email in PostgreSQL:

SELECT DISTINCT SUBSTRING(Email FROM '.+@(.*)$') 
FROM Email;

In this example, we used the SUBSTRING function to extract the Domain name of the Email address from the table Email. We also used the DISTINCT keyword to display only the distinct names.

In this section, we learned about PostgreSQL RegEx SUBSTRING() function.

Read PostgreSQL TO_NUMBER() function

Postgres Regex Numbers Only

In this section, we will learn about PostgreSQL Regex Numbers Only. We can use the REGEXP_REPLACE function to extract only the numbers from a string in PostgreSQL.

We will look at an example of the REGEXP_REPLACE() function to extract all the occurrences of numeric data type.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the REGEXP_REPLACE() function to extract the numeric type data in PostgreSQL:

SELECT REGEXP_REPLACE(Email,'\D','','g')
FROM Email;

It is clear from the screenshot that we have extracted only the numeric type data from the field Email in PostgreSQL using the REGEXP_REPLCAE function. Here “\D” is class shorthand for “Not a Digit”.

In this section, we learned about PostgreSQL Regex Numbers Only.

Read: PostgreSQL DATE Functions 

Postgres Regex Split

In this section, we will learn about PostgreSQL RegEX Split. SPLIT_PART() function in PostgreSQL is used to split a string into different parts. We need to pass the String, the Delimiter and the FIled Number to split a string into different parts. The syntax for SPLIT_PART() function in PostgreSQL is:

SPLIT_PART(STRING,DELIMITER,FIELD_NUMBER)

We will look at an example where we will Split a field of the Email table using the SPLIT_PART() function in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the SPLIT_PART() function to split a string into different parts:

SELECT SPLIT_PART(Email,'@',1)
FROM Email;

SELECT SPLIT_PART(Email,'@',2)
FROM Email;

In this example, we have used the SPLIT_PART() function to split the Email field of the Email table into two parts using the” @ ” delimiter. When we pass the FIELD_NUMBER as “1” it returns the text before the delimiter and when we pass the FIELD_NUMBER as “2” it returns the text after the delimiter.

In this section, we learned about PostgreSQL Regex SPLIT_PART() function.

Read: PostgreSQL ADD COLUMN

Postgres Regex Remove Special Characters

In this section, we will learn about PostgreSQL Regex Remove Special Characters. We can remove all the Special Characters from a given string using the REGEXP_REPLACE() function. We will look at an example where we will use the REGEXP_REPLACE function to remove all the occurrences of Special Characters from a field of a table in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;

Now we will use the REGEXP_REPLACE function to remove all the occurrences of any special characters from the Field:

SELECT REGEXP_REPLACE(Email, '[^\w]+','','g')
FROM Email;

In this example, we can see that all the occurrences of any special characters have been removed using the REGEXP_REPLACE() function.

You may also like to read the following articles:

• PostgreSQL vs SQL Server
• How to create database in PostgreSQL
• PostgreSQL WHERE IN
• PostgreSQL DROP TABLE + Examples
• PostgreSQL INSERT Multiple Rows
• PostgreSQL Rename Column

In this tutorial, we learned, about PostgreSQL RegEx. Also, we have covered these topics:

• Postgres Regex
• Postgres regexp_replace
• Postgres regexp_replace All Occurrences
• Postgres regexp_matches in WHERE Clause
• Postgres Regex Substring
• Postgres Regex Numbers Only
• Postgres Regex Split
• Postgres Regex Remove Special Characters