LIKE & Pattern Matching
Search for patterns in text data
The LIKE operator searches for patterns in strings using wildcards.
Wildcard Characters
| Wildcard | Meaning | Example |
|---|---|---|
| % | Zero or more characters | '%son' matches 'Johnson', 'son' |
| _ | Exactly one character | 'J_n' matches 'Jon', 'Jan' |
Common Patterns
-- Starts with SELECT * FROM users WHERE name LIKE 'John%'; -- Matches: John, Johnny, Johnson -- Ends with SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Matches: user@gmail.com, test.user@gmail.com -- Contains SELECT * FROM products WHERE name LIKE '%phone%'; -- Matches: iPhone, smartphone, phone case -- Exact length with wildcards SELECT * FROM users WHERE phone LIKE '___-___-____'; -- Matches: 555-123-4567 -- NOT LIKE SELECT * FROM users WHERE email NOT LIKE '%@test.com';
Case-Insensitive Search
-- Using LOWER (all databases) SELECT * FROM users WHERE LOWER(name) LIKE '%john%'; -- PostgreSQL: ILIKE (case-insensitive LIKE) SELECT * FROM users WHERE name ILIKE '%john%';
Escaping Wildcards
-- Search for literal % or _ SELECT * FROM products WHERE name LIKE '%50\%% off%' ESCAPE '\'; -- Finds: "50% off sale"
Performance Warning: Patterns starting with % (like '%search') cannot use indexes and can be very slow on large tables.