BEGINNER ⏱ 30 minutes

Pattern Matching with LIKE

So far you've learned to search for exact matches: WHERE name = 'Alice'. But what if you want to find all students whose names start with "A"? Or all email addresses ending in ".edu"? That's where LIKE comes in! LIKE lets you search for patterns in text, making it one of the most useful tools for real-world data searches.

📚 What You'll Learn

What the LIKE operator does and why it's essential
How to search text using flexible patterns
How the % wildcard works (matches any number of characters)
How the _ wildcard works (matches exactly one character)
Combining wildcards for complex patterns
Case sensitivity considerations in LIKE searches
NOT LIKE for exclusion searches
Common mistakes when using LIKE
Practical examples: searching names, emails, product descriptions, phone numbers
Performance considerations with LIKE
Hands-on practice exercises

1. What Does LIKE Do?

LIKE allows you to search for partial matches in text. Instead of finding exact matches like the = operator, LIKE finds patterns.

💡 Real-World Example: Imagine searching for a book on Amazon. You type "Harry Potter" but you don't remember the exact title—is it "Harry Potter and the Sorcerer's Stone" or "Harry Potter & the Philosopher's Stone"? A search engine uses pattern matching (like LIKE) to find both! Without LIKE, you'd have to type the exact title character-by-character.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

Comparison: = vs. LIKE

Exact Match (=)

WHERE name = 'Alice'

Finds ONLY "Alice" (exact match)

Pattern Match (LIKE)

WHERE name LIKE 'A%'

Finds "Alice", "Andrew", "Amanda", etc.

2. The % Wildcard (Zero or More Characters)

The % symbol is the most important wildcard. It matches any number of characters (including zero characters).

Pattern: Starts With

-- Find all names starting with "A"
SELECT name
FROM students
WHERE name LIKE 'A%';

Matches: Alice, Andrew, Amanda, Anthony, Aaron
Doesn't Match: Bob, Carol, Diana

Breaking it down: 'A%' means "starts with A, followed by anything (or nothing)"

Pattern: Ends With

-- Find all emails ending with ".edu"
SELECT name, email
FROM students
WHERE email LIKE '%.edu';

Matches: alice@university.edu, bob@college.edu, carol@school.edu
Doesn't Match: diana@gmail.com, emma@yahoo.com

Breaking it down: '%.edu' means "anything followed by .edu at the end"

Pattern: Contains

-- Find all product names containing "phone"
SELECT product_name, price
FROM products
WHERE product_name LIKE '%phone%';

Matches: iPhone, Samsung Galaxy phone, Headphones, Smartphone Case
Doesn't Match: Laptop, Tablet, Monitor

Breaking it down: '%phone%' means "anything, then 'phone', then anything"

Visual Guide to % Patterns

Pattern	Meaning	Example Matches
`'A%'`	Starts with A	Alice, Andrew, A
`'%son'`	Ends with son	Johnson, Anderson, son
`'%and%'`	Contains and	Andrew, Sandra, command
`'%'`	Matches anything	Everything (all rows)

3. The _ Wildcard (Exactly One Character)

The underscore _ matches exactly one character. This is useful when you know the length of what you're searching for but not the exact characters.

Example: Three-Letter Names

-- Find all three-letter names
SELECT name
FROM students
WHERE name LIKE '___';

Matches: Bob, Tom, Amy, Dan
Doesn't Match: Alice (5 letters), Jo (2 letters)

Breaking it down: '___' = 3 underscores = exactly 3 characters

Example: Phone Number Format

-- Find phone numbers in format: 555-01__
SELECT name, phone
FROM students
WHERE phone LIKE '555-01__';

Matches: 555-0100, 555-0123, 555-0199
Doesn't Match: 555-0200, 555-001 (too short)

Combining _ with %

-- Names starting with A, then exactly 3 more characters
SELECT name
FROM students
WHERE name LIKE 'A___';

Matches: Alex, Anna, Adam (4 letters total, starting with A)
Doesn't Match: Alice (5 letters), Amy (3 letters), Andrew (6 letters)

Visual Guide to _ Patterns

Pattern	Meaning	Example Matches
`'_a%'`	Second letter is 'a'	Sam, Carol, Harry
`'___%'`	At least 3 characters	Bob, Alice, Andrew
`'_____'`	Exactly 5 characters	Alice, Steve, James
`'A__%'`	Starts with A, at least 3 chars	Amy, Alice, Andrew

💡 Pro Tip: Use _ when you know the exact length, use % when the length varies. For example, '555-____' for 4-digit extensions, '555-%' for any length.

4. Combining Wildcards for Complex Patterns

You can combine % and _ to create sophisticated search patterns:

Example 1: Email Patterns

-- Find emails: first letter, then any chars, @university.edu
SELECT email
FROM students
WHERE email LIKE '__%@university.edu';

Matches: alice@university.edu, bob@university.edu
Doesn't Match: a@university.edu (only 1 char before @)

Example 2: Product Codes

-- Find product codes: XX-####-XX (2 letters, 4 digits, 2 letters)
SELECT product_code, product_name
FROM products
WHERE product_code LIKE '__-____-__';

Matches: AB-1234-CD, XY-9876-ZQ
Note: This matches the format, but doesn't verify letters vs. digits

Example 3: Middle Character Search

-- Names with 'e' as the third character
SELECT name
FROM students
WHERE name LIKE '__e%';

Matches: Steven, Alexis, Chelsea
Breaking it down: Any 2 chars, then 'e', then anything

5. Case Sensitivity in LIKE

LIKE behavior with uppercase/lowercase depends on your database:

MySQL: Case-insensitive by default (most common)
PostgreSQL: Case-sensitive by default
Oracle: Case-sensitive by default
SQL Server: Depends on collation settings

Making Case-Insensitive Searches

-- Convert both to lowercase for reliable case-insensitive search
SELECT name
FROM students
WHERE LOWER(name) LIKE LOWER('a%');

This works across all databases! Both the column and pattern are converted to lowercase before comparison.

⚠️ Performance Note: Using LOWER() on a column prevents the database from using indexes, which can slow down large queries. For production systems with millions of rows, consider storing a lowercase copy of searchable fields.

6. Using NOT LIKE for Exclusions

NOT LIKE finds rows that don't match the pattern:

-- Find students with non-.edu email addresses
SELECT name, email
FROM students
WHERE email NOT LIKE '%.edu';

Matches: alice@gmail.com, bob@yahoo.com, carol@outlook.com
Doesn't Match: diana@university.edu, emma@college.edu

Another Example: Excluding Test Data

-- Exclude test accounts (names starting with "test")
SELECT name, email
FROM users
WHERE name NOT LIKE 'test%';

7. Practical Real-World Examples

Searching Names

-- Find all "Johnson" variants
SELECT name FROM students 
WHERE name LIKE '%johnson%';
-- Matches: Johnson, Johnston, Ericjohnson, etc.

-- Find names starting with "Mc" or "Mac"
SELECT name FROM students 
WHERE name LIKE 'Mc%' OR name LIKE 'Mac%';
-- Matches: McDonald, MacGregor, McCarthy

Searching Email Addresses

-- Find all Gmail users
SELECT name, email FROM students 
WHERE email LIKE '%@gmail.com';

-- Find emails with numbers
SELECT name, email FROM students 
WHERE email LIKE '%0%' 
   OR email LIKE '%1%' 
   OR email LIKE '%2%';
-- (There's a better way with REGEXP, covered in advanced lessons!)

Searching Product Descriptions

-- Find laptops with specific features
SELECT product_name, description, price
FROM products
WHERE description LIKE '%laptop%' 
  AND (description LIKE '%16GB%' OR description LIKE '%32GB%');
-- Matches: "Dell Laptop with 16GB RAM", "HP Laptop 32GB"

Searching Phone Numbers

-- Find all phone numbers with area code 555
SELECT name, phone FROM students 
WHERE phone LIKE '555-%';

-- Find phone numbers in specific format
SELECT name, phone FROM students 
WHERE phone LIKE '___-___-____';
-- Matches: 555-123-4567, 800-555-1234

8. Common Mistakes When Using LIKE

Mistake #1: Forgetting the Wildcards

-- ❌ WRONG: This is exact match, not pattern match!
WHERE name LIKE 'Alice'

-- ✅ CORRECT: Use = for exact matches
WHERE name = 'Alice'

-- ✅ CORRECT: Use wildcards for patterns
WHERE name LIKE 'A%'

Mistake #2: Wrong Wildcard

-- ❌ WRONG: * is not a wildcard in SQL!
WHERE name LIKE 'A*'

-- ✅ CORRECT: Use % in SQL
WHERE name LIKE 'A%'

Note: SQL uses %, not * like some other programming languages!

Mistake #3: Not Handling NULL

-- ⚠️ INCOMPLETE: Misses rows where email is NULL
SELECT name FROM students WHERE email LIKE '%@gmail.com';

-- ✅ BETTER: Explicitly decide what to do with NULL
SELECT name FROM students 
WHERE email LIKE '%@gmail.com' OR email IS NULL;

Mistake #4: Using LIKE for Numbers

-- ❌ BAD: LIKE with numbers can give unexpected results
WHERE age LIKE '2%'  -- Finds 2, 20-29, 200-299, etc.

-- ✅ BETTER: Use comparison operators for numbers
WHERE age BETWEEN 20 AND 29

Mistake #5: Overusing LIKE for Performance

-- ❌ SLOW: Leading wildcard prevents index usage
WHERE email LIKE '%@gmail.com'

-- ✅ FASTER: Trailing wildcard can use indexes
WHERE email LIKE 'alice%'

-- ✅ BEST: Exact match is fastest
WHERE email = 'alice@gmail.com'

⚠️ Performance Tip: Patterns starting with % (like '%text' or '%text%') can't use indexes and are slow on large tables. When possible, put fixed text at the beginning: 'text%' is much faster than '%text'.

9. 🎯 Hands-On Practice Exercises

Exercise 1: Starts With Pattern

Task: Find all students whose names start with "J".

Show Answer

SELECT name
FROM students
WHERE name LIKE 'J%';

Exercise 2: Ends With Pattern

Task: Find all students with email addresses ending in ".org".

Show Answer

SELECT name, email
FROM students
WHERE email LIKE '%.org';

Exercise 3: Contains Pattern

Task: Find all students whose names contain "son" anywhere in the name.

Show Answer

SELECT name
FROM students
WHERE name LIKE '%son%';

Matches: Johnson, Anderson, Jason, Samson

Exercise 4: Using the _ Wildcard

Task: Find all students with exactly 4-letter names.

Show Answer

SELECT name
FROM students
WHERE name LIKE '____';

Note: That's four underscores!

Exercise 5: Combining Wildcards

Task: Find students whose names start with "A" and have at least 5 letters total.

Show Answer

SELECT name
FROM students
WHERE name LIKE 'A____%';

Breaking it down: A + 4 underscores = at least 5 chars total, + % for any additional

Exercise 6: NOT LIKE

Task: Find students who do NOT have .edu email addresses.

Show Answer

SELECT name, email
FROM students
WHERE email NOT LIKE '%.edu';

Exercise 7: Second Character Pattern

Task: Find students whose second letter of their name is "a".

Show Answer

SELECT name
FROM students
WHERE name LIKE '_a%';

Matches: Sam, Carol, James, David

Challenge Exercise: Complex Pattern

Task: Find products whose name contains "phone" but does NOT start with "Smart".

Show Answer

SELECT product_name
FROM products
WHERE product_name LIKE '%phone%'
  AND product_name NOT LIKE 'Smart%';

Matches: "iPhone", "Headphones", "Telephone"
Doesn't Match: "Smartphone", "Smart phone case"

📝 Key Takeaways

LIKE allows pattern matching in text searches
% matches any number of characters (including zero)
_ matches exactly one character
'A%' = starts with A, '%son' = ends with son, '%and%' = contains and
Combine wildcards: 'A____%' = starts with A, at least 5 chars
Use NOT LIKE to exclude patterns
Case sensitivity varies by database—use LOWER() for reliability
Leading % (like '%text') can be slow on large tables
Don't use LIKE without wildcards—just use = instead
Remember: SQL uses % and _, not * and ?

← Back to Lessons

Practice Now →