LIKE and ILIKE: Pattern Matching
The LIKE keyword filters rows that match a pattern.
It works with text values and uses wildcard characters.
Syntax
SELECT column1, column2, ... FROM table_name
WHERE column LIKE 'pattern';
Wildcards:
| Wildcard | Meaning |
|---|---|
% | Matches zero or more characters |
_ | Matches exactly one character |
Example 1: Names starting with 'A'
SELECT name FROM employees
WHERE name LIKE 'A%';
Output:
name
-------
Alice
Anna
(2 rows)
Example 2: Names ending with 'a'
SELECT name FROM employees
WHERE name LIKE '%a';
Output:
name
--------
Emma
Olivia
Tina
Anna
(4 rows)
Example 3: Names with exactly 3 letters
SELECT name FROM employees
WHERE name LIKE '___';
Output:
name
------
Bob
Leo
(2 rows)
Each _ matches exactly one character, so '___' matches names that are 3 characters long.
Example 4: Names containing 'li'
SELECT name FROM employees
WHERE name LIKE '%li%';
Output:
name
--------
Alice
Olivia
(2 rows)
With % on both sides, 'li' can be anywhere in the name.
Example 5: Names where the second letter is 'a'
SELECT name FROM employees
WHERE name LIKE '_a%';
Output:
name
-------
David
Jack
Sarah
Paul
(4 rows)
LIKE is Case-Sensitive in PostgreSQL
In PostgreSQL, LIKE cares about capital letters. Some other databases, like MySQL, do not. Here, 'a%' does not match Alice, because Alice starts with a capital A:
SELECT name FROM employees
WHERE name LIKE 'a%';
Output:
name
------
(0 rows)
PostgreSQL has ILIKE for this. It works like LIKE, but ignores case:
SELECT name FROM employees
WHERE name ILIKE 'a%';
Output:
name
-------
Alice
Anna
(2 rows)
Tip
You can combine LIKE with NOT to exclude a pattern, like name NOT LIKE 'A%'.
In the exercises, match names with LIKE and ILIKE patterns.