By the end of this lesson you can filter text with LIKE, ILIKE, NOT LIKE and the % and _ wildcards, match messy values regardless of case, and escape a literal % or _.
Why it matters: Exact matches fail on real text: product names contain the word you need somewhere in the middle, customers type coupon codes in any case, and email domains sit at the end of a string. Pattern matching is how analysts search text in SQL.
Dataset: Wadi Market online store. Examples run in the editor on the right.
= only matches a value that is exactly equal. LIKE compares text with a pattern containing wildcards:
%matches any sequence of characters, including none at all._(underscore) matches exactly one character.
Everything else in the pattern must match literally, and the pattern must match the whole value, from first character to last. That's why % usually appears at the start, the end, or both:
| pattern | matches |
|---|---|
'Najm%' | starts with Najm |
'%Laptop' | ends with Laptop |
'%Coffee%' | contains Coffee anywhere |
'NATIONALDAY9_' | NATIONALDAY9 followed by exactly one character |
WHERE column LIKE 'pattern' -- case-sensitive
WHERE column ILIKE 'pattern' -- case-insensitive (PostgreSQL)
WHERE column NOT LIKE 'pattern'SELECT product_name, unit_price
FROM products
WHERE product_name LIKE '%Smartphone%';The pattern means: anything, then the word Smartphone, then anything. All three Najm smartphones match, whatever comes before or after the word. Without the % signs, only a product called exactly 'Smartphone' would match.
SELECT DISTINCT coupon_code
FROM orders
WHERE coupon_code LIKE 'NATIONALDAY9_';_ stands for exactly one character, so the result contains the National Day codes for different years (NATIONALDAY93, 94 and 95). A code typed with an extra trailing space would not match, because the pattern allows only one character after the 9.
Case: LIKE versus ILIKE
LIKE is case-sensitive in PostgreSQL: '%coffee%' does not match 'Saudi Coffee with Cardamom'. PostgreSQL adds ILIKE, the case-insensitive version, which is ideal for values typed by people. Wadi Market's coupon codes are typed by customers, so the same code appears as BIZ5, biz5 and BIZ5 (with a trailing space). A pattern with a trailing % also catches the stray space.
SELECT DISTINCT coupon_code
FROM orders
WHERE coupon_code ILIKE 'biz5%';ILIKE ignores case and the trailing % absorbs the stray space, so the clean code and its messy variants (lowercase, trailing space) all come back. Try the same query with LIKE 'BIZ5%': the lowercase variant disappears.
When the data contains % or _
Sometimes you need to search for a literal underscore or percent sign. Wadi Market's payment methods include cash_on_delivery, bank_transfer and apple_pay. The pattern '%_%' does not mean contains an underscore: _ is a wildcard, so it means at least one character, which matches every method. Put a backslash in front of the wildcard to make it literal: '%\_%'. If backslashes are awkward in your tool, choose your own escape character: LIKE '%!_%' ESCAPE '!'.
NULL and performance
A NULL value never matches LIKE or NOT LIKE (the result is unknown), so customers without an email appear in neither list. And a pattern that starts with % forces PostgreSQL to check every row, because a normal index can't help when the beginning is unknown. On small tables it doesn't matter; on large ones it's a reason to store searchable values cleanly.
SELECT DISTINCT method
FROM payments
WHERE method LIKE '%\_%';The backslash makes the underscore literal, so only methods whose name contains an underscore remain: apple_pay, bank_transfer and cash_on_delivery. Remove the backslash and mada and card come back too, because _ would match any single character.