🌱BeginnerFiltering & Sorting
LIKE and Pattern Matching
Filter text columns using wildcard patterns with LIKE and ILIKE.
Overview
LIKE allows pattern matching on text columns using two wildcards: % (matches any sequence of characters, including empty) and _ (matches exactly one character). It is commonly used for searching text for example, finding all emails from a particular domain or names starting with a certain prefix.
PostgreSQL also provides ILIKE, which is identical to LIKE but case-insensitive. SIMILAR TO supports a richer pattern language (SQL regular expressions), and ~ supports full POSIX regular expressions. For serious text search, use pg_trgm with GIN indexes for fast LIKE/ILIKE on large tables.
Key Points
- •% matches any string (zero or more characters)
- •_ matches exactly one character
- •ILIKE is case-insensitive LIKE (PostgreSQL extension)
- •NOT LIKE / NOT ILIKE negates the match
- •LIKE on unindexed large columns is slow use pg_trgm GIN index for fast ILIKE
- •SIMILAR TO supports SQL regex; ~ supports POSIX regex
Examples
LIKE wildcard patterns
-- Starts with "Jo"
SELECT * FROM users WHERE name LIKE 'Jo%';
-- Ends with ".com"
SELECT * FROM users WHERE email LIKE '%.com';
-- Contains "admin" anywhere
SELECT * FROM users WHERE email LIKE '%admin%';
-- Exactly 5 characters
SELECT * FROM codes WHERE code LIKE '_____';ILIKE for case-insensitive search
-- Matches "alice", "Alice", "ALICE", etc.
SELECT * FROM users WHERE name ILIKE 'alice';
-- Case-insensitive prefix search
SELECT * FROM products WHERE name ILIKE 'widget%';Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4