Topic 11 of 52
LIKE Pattern Matching
Overview
Sometimes you don't know the exact string you are looking for. Maybe you need to find all users with a Gmail address, or a product name containing the word 'Laptop'. The LIKE operator is SQL's native string pattern-matching engine. It uses wildcards (% for any number of characters, and _ for exactly one character) to perform fuzzy searches.
Syntax
sql
-- The % wildcard represents ZERO, ONE, or MULTIPLE characters.
-- 1. Starts with 'A'
SELECT * FROM users WHERE name LIKE 'A%';
-- 2. Ends with '@gmail.com'
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- 3. Contains the word 'apple' anywhere in the string
SELECT * FROM products WHERE description LIKE '%apple%';
-- The _ (underscore) wildcard represents EXACTLY ONE character.
-- 4. Starts with 'C', has exactly two letters in the middle, and ends with 't'
-- Matches 'Cat', 'Coat', 'Cart'. Will NOT match 'Carrot'.
SELECT * FROM words WHERE text LIKE 'C__t';Common Pitfalls
- Using leading wildcards (
LIKE '%apple'). This is a catastrophic performance killer. If you start a search pattern with a%, the database CANNOT use standard B-Tree Indexes. It is forced to scan every single row in the massive table (Full Table Scan), bringing large databases to a crawl. - Forgetting about case-sensitivity. In PostgreSQL,
LIKEis strictly case-sensitive ('Apple' does not match '%apple%'). You must useILIKEfor case-insensitive searches.
Interview Questions
Q:
If
LIKE is too slow for searching through millions of massive blog post text bodies, what should you use instead?A:
Full-Text Search (FTS). Standard LIKE scans strings character by character. FTS engines (like Postgres tsvector or Elasticsearch) tokenize words and create an inverted index, allowing instant searches across massive documents.
Real-World Example
Finding all US Phone Numbers with a specific area code.
example
sql
-- The phone number format is (XXX) YYY-ZZZZ
-- We want anyone with a 206 area code.
SELECT user_id, phone
FROM contacts
WHERE phone LIKE '(206)%';Check Your Knowledge
Test your understanding of LIKE Pattern Matching with these quick questions.