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:

WildcardMeaning
%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.