PostgreSQL SIMILAR TO Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL SIMILAR TO operator to search for a specific pattern in a column that matches a regular expression.

Please note that you need a basic understanding of regular expressions to continue this tutorial.

Overview of the PostgreSQL SIMILAR TO Operator #

In PostgreSQL, the SIMILAR TO operator returns true if the regular expression matches the whole string.

Here’s the syntax of the SIMILAR TO operator:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  column1 SIMILAR TO pattern;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the SIMILAR TO operator returns true only if the value in the pattern matches the entire value in column1, or false otherwise.

It differs from the expected behavior of matching a string with a regular expression, which can match any part of a string.

Besides metacharacters used in a regular expression, you can use the _ and % wildcard characters to denote a single character and zero or more characters, respectively.

Typically, the dot (.) character is a metacharacter that matches any single character except the newline, but the SIMILAR TO operator does not treat it as a metacharacter.

Example of PostgreSQL SIMILAR TO Operator #

Let’s take some examples of using the SIMILAR TO operator with the products table:

The following SELECT statement uses the SIMILAR TO operator to select products with names that match a regular expression:

SELECT
  product_name
FROM
  products
WHERE
  product_name SIMILAR TO '\w+\s*(Galaxy|iPhone)\s*\w+';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name
--------------------
 Samsung Galaxy S24
 Apple iPhone 15Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s break down the regular expression '\w+\s*(Galaxy|iPhone)\s*\w+':

• \w+ matches one or more words.
• \s* matches zero or more spaces.
• (Galaxy|iPhone) matches either the word Galaxy or iPhone.
• \s* matches zero or more spaces.
• \w+ matches one or more words.

So, the regular expression matches any product name that starts with one or more words, a space, either Galaxy or iPhone, a space, and one or more words.

Using NOT SIMILAR TO Operator #

To negate the SIMILAR TO operator, you use the NOT operator:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  column1 NOT SIMILAR TO pattern;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The NOT SIMILAR TO operator returns true if the value in column1 does not match the pattern or false otherwise.

For example, the following query uses the NOT SIMILAR TO operator to retrieve the product names for all products with names that do not end with one or more digits:

SELECT
  product_name
FROM
  products
WHERE
  product_name NOT SIMILAR TO '%\d+';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name
---------------------------
 Sony Xperia 1 VI
 Apple iPhone 15 Pro Max
 Sony Bravia XR A95K
 Samsung QN900C Neo QLED
 LG G3 OLED
 Sony HT-A7000 Soundbar
 Bose SoundLink Max
 Lenovo ThinkPad X1 Carbon
 Apple iMac 24"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• % matches zero or more characters. Note that the % is the wildcard character, not the metacharacter used in the regular expression.
• \d+ matches one or more digits.

The pattern '%\d+' matches any strings that start with zero or more characters and end with one or more digits. The NOT SIMILAR TO returns the products whose names do not end with digits.

Using ESCAPE in SIMILAR TO Operator #

Like the LIKE operator, you can use the ESCAPE option to define an escape character in the pattern when using the SIMILAR TO operator:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  column1 SIMILAR TO pattern ESCAPE escape_character;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement uses the SIMILAR TO with the ESCAPE option to find products whose descriptions contain the character %:

SELECT
  product_name,
  description
FROM
  products
WHERE
  description SIMILAR TO '%$%%*' ESCAPE '$';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example:

• The first and last % characters are wildcard characters.
• The second % is the wildcard, but we want to treat it as a regular character. To do that, we specify an escape character $ in the ESCAPE option and use the escape character $ right before the % in the pattern.

Summary #

• Use the SIMILAR TO operator to search for a value that matches a regular expression.
• Use the NOT operator to negate the result of the SIMILAR TO operator.

Quiz #