Lesson Beginner

LIKE

= checks for exact equality.
LIKE lets you match text by pattern: prefixes, suffixes, substrings, and single-character gaps. It is how you answer questions like "emails ending in @gmail.com" or "product names starting with Laptop".

Two Wildcards #

LIKE has exactly two wildcards:

Wildcard	Meaning
`%`	Zero or more characters
`_`	Exactly one character

Everything else in the pattern is a literal character that must match exactly.

sql

-- Starts with "Laptop"
SELECT name FROM products WHERE name LIKE 'Laptop%';

-- Contains "Pro"
SELECT name FROM products WHERE name LIKE '%Pro%';

-- Ends in "15"
SELECT name FROM products WHERE name LIKE '%15';

-- Any single character, then "ouse"
SELECT name FROM products WHERE name LIKE '_ouse';

Case Sensitivity #

LIKE is case-sensitive in most dialects, including Postgres and DuckDB.
For case-insensitive matching, use ILIKE. It works exactly the same, but ignores case.

sql

-- Case-sensitive: 'laptop pro' will NOT match 'Laptop%'
SELECT name FROM products WHERE name LIKE 'Laptop%';

-- Case-insensitive: matches Laptop, LAPTOP, laptop, etc.
SELECT name FROM products WHERE name ILIKE 'laptop%';

SQL Server uses LIKE with a case-insensitive collation by default. MySQL usually does too. Moving a query between dialects and getting surprised by case is a classic bug.

Warning

A leading % defeats indexes.
LIKE 'Laptop%' can use an index on name. LIKE '%Laptop%' cannot, because the database has to scan every row. On small tables this is invisible; on a 100-million-row table it is the difference between 20ms and 20 minutes. When you can anchor the pattern to the start of the string, do.

Try It #

Match product names with different anchors and wildcards.

Loading SQL editor...

Practice #

Return id, name, and price for every product whose name starts with the word 'Laptop'. No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Forgetting LIKE is case-sensitive in Postgres and DuckDB. Use ILIKE when case should not matter.
Using LIKE '%value%' on very large tables when a prefix pattern would do.
Confusing LIKE with regex: ., *, +, and ? are literals in LIKE, not regex metacharacters. For real regex, reach for dialect-specific operators like ~ (Postgres) or regexp_matches (DuckDB).
Trying to match a literal % or _ without escaping.

Knowledge check #

4 questions

0 / 4 answered

In LIKE, what does % match?
WHERE name LIKE 'A%' matches:
Default LIKE on most engines is case-sensitive. For case-insensitive matching:
WHERE description LIKE '%50\%%' (with ESCAPE '\\') is trying to match:

Next Step #

Continue to NULL handling — the one value that breaks every filter rule you just learned.