Module 14 — LIKE & Pattern Matching

1. LIKE — Pattern Matching

What it is: LIKE is a comparison operator that matches text patterns using wildcards. Unlike = which requires an exact match, LIKE lets you search for partial matches — "starts with", "ends with", "contains", etc.

Why we use it: Exact matches are often too restrictive. When a user searches for "john", you want to match "John Doe", "Johnny", and "Johnson". LIKE handles these partial matches.

When we use it: In search functionality, filtering by partial text, validating data formats (emails ending in @domain.com), or any time you need pattern-based text matching.

The % wildcard represents "any characters" (including none).

-- Names starting with 'Al' (Alice, Alex, Alberto)
SELECT * FROM users WHERE username LIKE 'Al%';
 
-- Emails ending with '@gmail.com'
SELECT * FROM users WHERE email LIKE '%@gmail.com';
 
-- Names containing 'dev' (alice_dev, dev_bob, senior_dev)
SELECT * FROM users WHERE username LIKE '

2. Wildcards

What it is: Wildcards are special characters used in LIKE patterns. % matches any number of characters (including zero), and _ matches exactly one character. They're the building blocks of pattern matching.

Why we use it: Wildcards give you flexibility in matching — % at the end means "starts with", % at both ends means "contains", _ lets you match specific character positions.

When we use it: Every time you use LIKE — the pattern is built with wildcards to define what you're searching for.

-- Exactly 3-letter names
SELECT * FROM users WHERE username LIKE '___';
 
-- Second character is 'a'
SELECT * FROM users WHERE username LIKE '_a%';

3. LIKE vs ILIKE

What it is: LIKE is case-sensitive (standard SQL), while ILIKE is case-insensitive (PostgreSQL extension). ILIKE treats uppercase and lowercase letters as equivalent.

Why we use it: Users don't always type with correct capitalization. When someone searches for "john", they expect to find "John", "JOHN", and "john". ILIKE handles this automatically.

When we use it: Use ILIKE for user-facing search (search bars, filters). Use LIKE when case matters (validating codes, matching exact patterns).

-- Case-sensitive (won't match 'ALICE' or 'alice')
SELECT * FROM users WHERE username LIKE 'alice';
 
-- Case-insensitive (matches 'Alice', 'alice', 'ALICE')
SELECT * FROM users WHERE username ILIKE 'alice';

4. NOT LIKE / NOT ILIKE

What it is: NOT LIKE and NOT ILIKE exclude rows that match the pattern. They're the negation of LIKE and ILIKE — "does NOT match this pattern".

Why we use it: Sometimes you need to exclude patterns — "all users except admins", "all emails except test accounts", "all products not starting with 'TEST'".

When we use it: When filtering out specific patterns, excluding test data, or building negative filters.

-- Users whose name does NOT start with 'admin'
SELECT * FROM users WHERE username NOT LIKE 'admin%';
 
-- Exclude gmail addresses
SELECT * FROM users WHERE email NOT ILIKE '%@gmail.com';

5. LIKE with ESCAPE

What it is: Since % and _ are wildcards, searching for the literal characters % or _ requires an escape character. The ESCAPE clause defines which character is the escape prefix.

Why we use it: Some data contains literal special characters — file names with underscores, percentages in text, etc. Without ESCAPE, you can't search for these characters.

When we use it: When searching for data that contains literal wildcard characters — file names, codes, or formatted text.

-- Search for a literal underscore
SELECT * FROM products WHERE name LIKE '%\_%' ESCAPE '\';
 
-- Search for a literal percent sign
SELECT * FROM products WHERE name LIKE '%\%%' ESCAPE '\';

Common Mistakes to Avoid

1. Using LIKE for exact matches — Use = instead, it's faster
2. Forgetting ILIKE exists — Use it for user-facing search
3. LIKE '%something%' on large tables — Leading wildcards prevent index usage. Consider full-text search for large datasets.

Practice Exercise

Using your courses table:

1. Find all courses whose title starts with 'SQL'
2. Find all courses whose title contains 'Advanced' (case-insensitive)
3. Find all courses whose instructor name ends with 'Doe'
4. Find all courses whose title is exactly 10 characters long (use _)
5. Find all courses whose title does NOT contain 'Python'