PostgreSQL LIKE Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL LIKE operator to search for a specified pattern in a column.

Getting Started with the PostgreSQL LIKE operator #

The PostgreSQL LIKE operator allows you to search for a specified pattern in a column. You can use the LIKE operator in a WHERE clause to filter rows based on a pattern.

Here’s the syntax for the LIKE operator:

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

Wildcard Characters #

Typically, you use the following wildcard characters to construct a pattern:

% represents any zero or more characters.
_ represents any single character.

PostgreSQL LIKE Operator Examples #

Let’s explore examples of using the LIKE operator with the products table:

Finding Products with Names Starting with “Samsung” #

The following query uses the LIKE operator to find the products whose names start with "Samsung":

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name LIKE 'Samsung%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        |  price
---------------------------+---------
 Samsung Galaxy S24        |  999.99
 Samsung Galaxy Z Fold 5   | 1799.99
 Samsung Galaxy Tab S9     |  699.99
 Samsung Galaxy Buds Pro 2 |  199.99
 Samsung Galaxy Watch 6    |  349.99
 Samsung QN900C Neo QLED   | 2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The pattern “Samsung%” match products whose names start with the word "Samsung" .

Finding Products with Names Ending with “5” #

The following statement uses the LIKE operator to find the products whose names end with the string "5":

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name LIKE '%5';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price
----------------------------+---------
 Apple iPhone 15            | 1099.99
 Samsung Galaxy Z Fold 5    | 1799.99
 Dell XPS 15                | 1499.99
 Microsoft Surface Laptop 5 | 1299.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finding Products with “Pro” Anywhere in the Name #

The following query uses the LIKE operator to find all products with names containing the word "Pro":

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name LIKE '%Pro%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        |  price
---------------------------+---------
 Apple iPhone 15 Pro Max   | 1299.99
 Apple iPad Pro 12.9       | 1099.99
 Apple AirPods Pro 3       |  249.99
 Samsung Galaxy Buds Pro 2 |  199.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finding Products with Names Containing Exactly 10 Characters #

The following SELECT statement uses the LIKE operator to find the products with the name containing exactly 10 characters:

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name LIKE '__________';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_name |  price
--------------+---------
 LG G3 OLED   | 2499.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, each underscore _ represents a single character.

Using ILIKE Operator for Case-Insensitive Search #

PostgreSQL offers the ILIKE operator, which is similar to LIKE but matches a pattern case-insensitively.

For example, the following statement uses the ILIKE operator to find all products with names having the letter "B" or "b":

SELECT
  product_name,
  price
FROM
  products
WHERE
  product_name ILIKE '%b%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        |  price
---------------------------+---------
 Samsung Galaxy Tab S9     |  699.99
 Samsung Galaxy Buds Pro 2 |  199.99
 Sony Bravia XR A95K       | 2499.99
 Sony HT-A7000 Soundbar    | 1299.99
 Bose SoundLink Max        |  399.99
 Lenovo ThinkPad X1 Carbon | 1599.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Negating the LIKE Operator with the NOT Operator #

The NOT operator negates the result of the LIKE and ILIKE operators:

SELECT select_list
FROM table_name
WHERE column1 NOT LIKE pattern;

SELECT select_list
FROM table_name
WHERE column1 NOT ILIKE pattern;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement uses the NOT LIKE operator to find products whose names do not contain the letter 'a' or 'A':

SELECT
  product_name
FROM
  products
WHERE
  product_name NOT ILIKE '%a%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name
------------------
 LG OLED TV C3
 LG G3 OLED
 Dell XPS 15
 HP Spectre x360
 Dell Inspiron 27Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Operators #

PostgreSQL provides some operators that replicate the functionality of LIKE, NOT LIKE, ILIKE, and NOT ILIKE operators.

Operator	Meaning
`~~`	`LIKE`
`~~*`	`ILIKE`
`!~~`	`NOT LIKE`
`!~~*`	`NOT ILIKE`

For example:

SELECT
  product_name
FROM
  products
WHERE
  product_name ~~ '%3%';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name
---------------------
 Apple AirPods Pro 3
 LG OLED TV C3
 LG G3 OLED
 HP Spectre x360Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Escape Characters #

The string you want to find may contain the wildcard characters % and _ such as 100% .

To treat the wildcard characters as regular characters, you can use the ESCAPE option in the LIKE and ILIKE operators:

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

For example, the following statement uses the LIKE operator to find products with a description that contains the word "100%":

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

Try it

(please see the output in the playground)

In this example, we use the $ character as an escape character. We specify the escape character $ before % so the LIKE operator treats the character % that immediately follows 100 as a regular character.

Using LIKE with CASE expression #

The following shows how to use the LIKE operator in the CASE expression to retrieve the iPhone and Galaxy groups from the products table:

SELECT
  product_name,
  CASE
    WHEN product_name LIKE '%iPhone%' THEN 'iPhone'
    WHEN product_name LIKE '%Galaxy%' THEN 'Galaxy'
  END AS group,
  price
FROM
  products
WHERE
  product_name like '%iPhone%'
  OR product_name like '%Galaxy%'
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        | group  |  price
---------------------------+--------+---------
 Apple iPhone 15           | iPhone | 1099.99
 Apple iPhone 15 Pro Max   | iPhone | 1299.99
 Samsung Galaxy Buds Pro 2 | Galaxy |  199.99
 Samsung Galaxy S24        | Galaxy |  999.99
 Samsung Galaxy Tab S9     | Galaxy |  699.99
 Samsung Galaxy Watch 6    | Galaxy |  349.99
 Samsung Galaxy Z Fold 5   | Galaxy | 1799.99Code language: plaintext (plaintext)

In this example:

The WHEREfilters the rows to include only products with the name containing either "iPhone" or "Galaxy".
The CASE expression creates a new column named group based on the value of the product_name.

If the product name contains the word "iPhone" or "Galaxy" , the group column will have the value "iPhone" or "Galaxy" respectively.

Summary #

Use the LIKE operator to match a specified pattern in a column.
Use the % wildcard character to represent zero or more characters and the _ wildcard character to represent a single character.
Use the ILIKE operator to match a specified pattern case-insensitively.
Use the NOT operator to negate the LIKE and ILIKE operators.
Use the ESCAPE to treat wildcard characters as regular characters.

Quiz #

Was this tutorial helpful ?