PostgreSQL LIKE - Check if a text matches a specified pattern

In PostgreSQL, the LIKE operator is a boolean operator that checks whether a text matches a specified pattern. The LIKE operator returns true if the text matches the specified pattern, otherwise it returns false.

The ILIKE operator is a case-insensitive version of the LIKE operator, which performs a case-insensitive match.

PostgreSQL LIKE Syntax

The LIKE operator is a binary comparison operator that requires two operands. This is the syntax of the LIKE operator:

expr LIKE pattern
expr ILIKE pattern

Explanation:

• The expr can be a field name, value, or expression (such as function calls, operations, etc.). The value of expr should be a text.

• The pattern is a string pattern. It supports two wildcards: % and _.

  • The % matches zero or more arbitrary characters.

  • The _ matches a single arbitrary character.

  • If you need to match the above two wildcards, you need to use \ escape them, such as \% and \_.

  • For example:

    • The a% matches a string of any length starting with a.
    • The %a matches a string of any length ending with a.
    • The %a% matches a string of any length containing a.
    • The %a%b% matches a string of any length that contains both a and b, and a is before b.
    • The a_ matches a string of length 2 starting with a.
    • The _a matches a string of length 2 ending with a.

The LIKE operator returns true if expr matches pattern and false otherwise.

NOT LIKE is the negation of the LIKE operator.

NOT ILIKE is the negation of the ILIKE operator.

PostgreSQL LIKE algorithm

PostgreSQL LIKE operator is used to detect whether a text matches a specified pattern. Its operation rules are as follows:

• Returns t if the operand on the left of the LIKE operator matches the pattern on the right. Otherwise returns f.

  SELECT
      'a' LIKE 'a'   "'a' LIKE 'a'",
      'a' LIKE 'a%'  "'a' LIKE 'a%'",
      'ab' LIKE 'a%' "'ab' LIKE 'a%'",
      'ab' LIKE '%a' "'ab' LIKE '%a'";
  

   'a' LIKE 'a' | 'a' LIKE 'a%' | 'ab' LIKE 'a%' | 'ab' LIKE '%a'
  --------------+---------------+----------------+----------------
   t            | t             | t              | f

  SELECT
      'a' LIKE 'a_'   "'a' LIKE 'a_'",
      'ab' LIKE 'a_'  "'ab' LIKE 'a_'",
      'abc' LIKE 'a_' "'abc' LIKE 'a_'";
  

   'a' LIKE 'a_' | 'ab' LIKE 'a_' | 'abc' LIKE 'a_'
  ---------------+----------------+-----------------
   f             | t              | f

• Returns NULL if either of the two operands of the LIKE operator is NULL.

  SELECT
      NULL LIKE 'a%' "NULL LIKE 'a%'",
      'a' LIKE NULL  "'a' LIKE NULL ";
  

   NULL LIKE 'a%' | 'a' LIKE NULL
  ----------------+----------------
   <null>         | <null>

PostgreSQL LIKE Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the following examples, we use actor and category tables from the Sakila sample database for demonstration purposes.

Use LIKE to match texts starting with a character

To find all actors whose names begin with P the characters from the actor table, use the following statement with LIKE operator:

SELECT *
FROM actor
WHERE first_name LIKE 'P%';

 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33
       46 | PARKER     | GOLDBERG  | 2006-02-15 04:34:33
       54 | PENELOPE   | PINKETT   | 2006-02-15 04:34:33
      104 | PENELOPE   | CRONYN    | 2006-02-15 04:34:33
      120 | PENELOPE   | MONROE    | 2006-02-15 04:34:33
(5 rows)

Here, the P% pattern represents any length of text starting with P. To find all actors whose names starting with P, we use the expression first_name LIKE 'P%'.

Use LIKE to match texts ending with some characters

To find all actors whose names ending with ES from the actor table, use the following statement with LIKE operator:

SELECT *
FROM actor
WHERE first_name LIKE '%ES';

 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
       48 | FRANCES    | DAY-LEWIS | 2006-02-15 04:34:33
       84 | JAMES      | PITT      | 2006-02-15 04:34:33
      126 | FRANCES    | TOMEI     | 2006-02-15 04:34:33
(3 rows)

Use LIKE to match texts containing some characters

To find all actors whose names containing AM, use the following statement with LIKE operator:

SELECT * FROM actor WHERE first_name LIKE '%AM%';

 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
       24 | CAMERON    | STREEP    | 2006-02-15 04:34:33
       63 | CAMERON    | WRAY      | 2006-02-15 04:34:33
       71 | ADAM       | GRANT     | 2006-02-15 04:34:33
       84 | JAMES      | PITT      | 2006-02-15 04:34:33
      111 | CAMERON    | ZELLWEGER | 2006-02-15 04:34:33
      132 | ADAM       | HOPPER    | 2006-02-15 04:34:33
      175 | WILLIAM    | HACKMAN   | 2006-02-15 04:34:33
(7 rows)

Use LIKE to match texts ending with characters

The wildcard _ matches any single character. The following SQL statement uses the LIKE operator to find all actors whose names are 3 characters and ending with AY.

SELECT * FROM actor WHERE first_name LIKE '_AY';

 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
       55 | FAY        | KILMER    | 2006-02-15 04:34:33
       64 | RAY        | JOHANSSON | 2006-02-15 04:34:33
      147 | FAY        | WINSLET   | 2006-02-15 04:34:33
      156 | FAY        | WOOD      | 2006-02-15 04:34:33
(4 rows)

Conclusion

The PostgreSQL LIKE operator is a boolean operator that checks whether a text matches a specified pattern. The LIKE operator returns true if the text matches the specified pattern, otherwise it returns false.

The ILIKE operator is a case-insensitive version of the LIKE operator, which performs case-insensitive matching.