Pattern Matching with LIKE, ILIKE, and SIMILAR TO
You'll learn how to search for text patterns using SQL's powerful matching operators for flexible string queries.
The Core Idea
Sometimes you don't know the exact string you're searching for—you just know part of it or its general pattern. SQL's pattern matching operators let you find strings that resemble what you're looking for, not just exact matches. Think of it like using a search engine with wildcards instead of typing an entire phrase.
LIKE: Basic Pattern Matching
LIKE uses two wildcards:
%matches any sequence of characters (including zero)_matches exactly one character
SELECT name
FROM customers
WHERE email LIKE '%@gmail.com'; -- Ends with @gmail.com
SELECT product_name
FROM products
WHERE product_name LIKE 'Laptop_%'; -- Starts with "Laptop_" + one char
Note: LIKE is case-sensitive in most databases.
ILIKE: Case-Insensitive Matching
ILIKE works exactly like LIKE, but ignores case differences. Perfect when you don't care about capitalization.
SELECT title
FROM articles
WHERE title ILIKE '%data science%'; -- Matches "Data Science", "DATA SCIENCE", etc.
SIMILAR TO: SQL Pattern Matching with Regex-Like Features
SIMILAR TO combines SQL wildcards with limited regular expression syntax. Use | for OR, [] for character classes, and {} for repetition.
SELECT phone
FROM contacts
WHERE phone SIMILAR TO '[0-9]{3}-[0-9]{3}-[0-9]{4}'; -- Matches 123-456-7890
SELECT code
FROM products
WHERE code SIMILAR TO '(A|B)%'; -- Starts with A or B
Key Takeaway: Use LIKE for simple wildcard searches, ILIKE when case doesn't matter, and SIMILAR TO when you need more sophisticated pattern matching with character classes and alternation—all without leaving SQL.