August 1, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
ILIKE operators to query data using pattern matchings.
Introduction to PostgreSQL LIKE operator
Suppose that you want to find a customer, but you don’t remember her name exactly. However, you can recall that her name begins with something like
How do you find the exact customer from the database? You may find the customer in the
customer table by looking at the first name column to see if there is any value that begins with
Jen. However, this process can be time-consuming if the customer table has a large number of rows.
Fortunately, you can use the PostgreSQL
LIKE operator to match the first name of the customer with a string using the following query:
SELECT first_name, last_name FROM customer WHERE first_name LIKE 'Jen%';
Notice that the
WHERE clause contains a special expression: the
LIKE operator and a string that contains a percent sign (
%). The string
'Jen%' is called a pattern.
The query returns rows whose values in the first_name column begin with
Jen and may be followed by any sequence of characters. This technique is called pattern matching.
You construct a pattern by combining literal values with wildcard characters and use the
NOT LIKE operator to find the matches. PostgreSQL provides you with two wildcards:
- Percent sign (
%) matches any sequence of zero or more characters.
- Underscore sign (
_) matches any single character.
The syntax of PostgreSQL
LIKE operator is as follows:
value LIKE pattern
The expression returns true if the
value matches the
To negate the
LIKE operator, you use the
NOT operator as follows:
value NOT LIKE pattern
NOT LIKE operator returns true when the
value does not match the
If the pattern does not contain any wildcard character, the
LIKE operator behaves like the equal (
PostgreSQL LIKE operator – pattern matching examples
Let’s take some examples of using the
Simple PostgreSQL LIKE examples
See the following example:
SELECT 'foo' LIKE 'foo', -- true 'foo' LIKE 'f%', -- true 'foo' LIKE '_o_', -- true 'bar' LIKE 'b_'; -- false
How it works.
- The first expression returns true because the
foopattern does not contain any wildcard character so the
LIKEoperator acts like the equal (
- The second expression returns true because it matches any string that begins with the letter
fand followed by any number of characters.
- The third expression returns true because the pattern (
_o_) matches any string that begins with any single character, followed by the letter
oand ended with any single character.
- The fourth expression returns false because the pattern
b_matches any string that begins with the letter
band followed by any single character.
It’s possible to use wildcards at the beginning and/or end of the pattern.
For example, the following query returns customers whose first name contains
er string like
SELECT first_name, last_name FROM customer WHERE first_name LIKE '%er%' ORDER BY first_name;
You can combine the percent (
%) with underscore (
_) to construct a pattern as the following example:
SELECT first_name, last_name FROM customer WHERE first_name LIKE '_her%' ORDER BY first_name;
_her% matches any string that:
- Begin with any single character (
- And is followed by the literal string
- And is ended with any number of characters.
The returned first names are Cheryl, Sherri, Sherry, and Therasa.
PostgreSQL NOT LIKE examples
The following query uses the
NOT LIKE operator to find customers whose first names do not begin with
SELECT first_name, last_name FROM customer WHERE first_name NOT LIKE 'Jen%' ORDER BY first_name
PostgreSQL extensions of LIKE operator
PostgreSQL supports the
ILIKE operator that works like the
LIKE operator. In addition, the
ILIKE operator matches value case-insensitively. For example:
SELECT first_name, last_name FROM customer WHERE first_name ILIKE 'BAR%';
BAR% pattern matches any string that begins with
BaR, etc. If you use the
LIKE operator instead, the query will not return any row.
PostgreSQL also provides some operators that act like the
NOT ILIKE operator as shown below:
In this tutorial, you have learned how to use the PostgreSQL
ILIKE operators to query data using pattern matching.