Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
LIKE & Wildcards
LIKE searches for patterns in text values using wildcards. % matches any sequence of characters. _ matches exactly one character. Use NOT LIKE to exclude patterns. ILIKE (PostgreSQL) makes the search case-insensitive.
LIKE Wildcard Rules
- % - matches zero or more characters of any kind
- _ - matches exactly one character
- LIKE 'A%' - starts with A
- LIKE '%son' - ends with son
- LIKE '%phone%' - contains phone anywhere
- LIKE 'T_m' - T, any one char, then m (e.g. Tom, Tim)
- Case-sensitive in MySQL by default, case-sensitive in PostgreSQL
- ILIKE (PostgreSQL) - case-insensitive LIKE
LIKE Examples
-- Products containing 'phone' in name (case-sensitive)
SELECT name FROM products WHERE name LIKE '%phone%';
-- Case-insensitive (PostgreSQL ILIKE)
SELECT name FROM products WHERE name ILIKE '%phone%';
-- Result: Phone, iPhone, headphone - matches all cases
-- Starts with 'L'
SELECT name FROM products WHERE name LIKE 'L%';
-- Result: Laptop
-- Ends with 's'
SELECT name FROM products WHERE name LIKE '%s';
-- Result: Headphones
-- Exactly 5 characters
SELECT name FROM products WHERE name LIKE '_____';
-- Email domain filter
SELECT name, email FROM users WHERE email LIKE '%@example.com';
-- Result: John Doe, Alice Smith, Bob Lee
-- NOT LIKE
SELECT name FROM products WHERE name NOT LIKE '%head%';
-- Result: Laptop, Phone (excludes Headphones)
-- Pattern with underscore: match 'J_n' (Jon, Jan, Jun)
SELECT name FROM users WHERE name LIKE 'J_n%';
-- Escape a literal % using ESCAPE
SELECT * FROM promo_codes WHERE code LIKE '50%%' ESCAPE '\';
-- Matches '50%OFF', '50%SALE'Quick Quiz
Tip
Tip
Practice LIKE Wildcards in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with LIKE Wildcards is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of LIKE Wildcards from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- LIKE searches for patterns in text values using wildcards.
- % - matches zero or more characters of any kind
- _ - matches exactly one character
- LIKE 'A%' - starts with A