LIKE and ILIKE

Objectives

By the end of this lesson, you should be able to:

• Match text by pattern with LIKE
• Use % and _ wildcards correctly
• Use ILIKE for case-insensitive matching

💡 Why this matters: Not every text search is an exact match. “Names starting with J,” “emails containing ’example’,” these need pattern matching, not =.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier in this module.

LIKE and the % Wildcard

SELECT first_name FROM employees WHERE first_name LIKE 'J%';

 first_name
------------
 Jordan

% matches any sequence of characters, including none at all. 'J%' means “starts with J, followed by anything.” Put % on both sides to match a substring anywhere in the text:

SELECT first_name FROM employees WHERE first_name LIKE '%or%';

 first_name
------------
 Jordan
 Taylor

Both Jordan and Taylor contain or somewhere in the middle. LIKE is case-sensitive by default, '%OR%' would match neither of these.

The _ Wildcard

_ (a single underscore) matches exactly one character, where % matches any number of them. '_a%' matches any name with a as its second letter, Maya, Sam, and Taylor all qualify, Erin (second letter r) doesn’t.

ILIKE: Case-Insensitive Matching

SELECT first_name FROM employees WHERE first_name ILIKE 'ERIN';

 first_name
------------
 Erin

ILIKE is PostgreSQL’s case-insensitive version of LIKE, everything else about wildcards works identically. 'erin', 'ERIN', and 'Erin' all match the same rows with ILIKE 'erin', useful for search features where users shouldn’t need to match capitalization exactly. Plain LIKE is a PostgreSQL-specific… actually LIKE is standard SQL, ILIKE specifically is a PostgreSQL extension, not every RDBMS (Module 1) has it, some rely on collation settings instead for case-insensitive matching.

Try It

1. Write a query for every employee whose first_name starts with M.
2. Write a query for every employee whose email contains 'castillo' anywhere in it.
3. Write a query using ILIKE that matches 'devon' regardless of how it was capitalized in the actual data.
4. Explain, in your own words, the difference between the % and _ wildcards.

Recap

• LIKE matches text by pattern, % matches any sequence of characters, _ matches exactly one.
• LIKE is case-sensitive, ILIKE (a PostgreSQL extension) is the case-insensitive equivalent.
• Wildcards can go anywhere in the pattern, at the start, the end, both, or the middle.

Next lesson: IS NULL, the one comparison ordinary operators can’t make.