WHERE with LIKE pattern matching in SQL - Deep Dive

Overview - WHERE with LIKE pattern matching

What is it?

The WHERE clause in SQL is used to filter rows from a table based on a condition. The LIKE operator allows you to search for a specific pattern within a column's text data. It uses special symbols called wildcards to match parts of the text, making it flexible for searching. This helps find rows where the text fits a pattern instead of exact matches.

Why it matters

Without the LIKE operator, searching for text in databases would be limited to exact matches, making it hard to find data when you only know part of the text or want to find similar entries. LIKE solves this by letting you find data based on patterns, which is useful in many real-life cases like searching names, emails, or product codes. Without it, users would struggle to get meaningful search results.

Where it fits

Before learning WHERE with LIKE, you should understand basic SQL SELECT queries and the WHERE clause for filtering. After mastering LIKE, you can learn more advanced pattern matching with regular expressions or full-text search features for even more powerful text searching.

Mental Model

Core Idea

The WHERE with LIKE clause filters rows by matching text patterns using wildcards inside a column.

Think of it like...

It's like using a magnifying glass to find words in a book that start with certain letters or contain certain parts, even if you don't know the whole word.

Table: Users
╔════╦════════════╗
║ ID ║ Name       ║
╠════╬════════════╣
║ 1  ║ Alice      ║
║ 2  ║ Bob        ║
║ 3  ║ Alicia     ║
║ 4  ║ Albert     ║
╚════╩════════════╝

Query: SELECT * FROM Users WHERE Name LIKE 'Al%';
Result:
╔════╦════════╗
║ 1  ║ Alice  ║
║ 3  ║ Alicia ║
║ 4  ║ Albert ║
╚════╩════════╝

Build-Up - 7 Steps

1

Foundation

Basic WHERE Clause Filtering

Concept: Learn how the WHERE clause filters rows based on exact matches.

The WHERE clause lets you pick rows that meet a condition. For example, SELECT * FROM Users WHERE Name = 'Alice'; returns only rows where the Name is exactly 'Alice'.

Result

Only rows with Name exactly 'Alice' are shown.

Understanding exact filtering is the base for more flexible pattern matching.

2

Foundation

Introduction to LIKE Operator

Concept: LIKE lets you match text patterns instead of exact text.

LIKE uses two wildcards: % means any number of characters, _ means exactly one character. For example, WHERE Name LIKE 'Al%' finds names starting with 'Al'.

Result

Rows with names starting with 'Al' are returned, like 'Alice' and 'Albert'.

LIKE expands filtering from exact matches to flexible pattern searches.

3

Intermediate

Using % Wildcard for Flexible Matching

🤔Before reading on: do you think 'A%' matches only names starting with 'A' or also names containing 'A' anywhere? Commit to your answer.

Concept: % matches zero or more characters anywhere in the pattern.

The % wildcard matches any number of characters, including none. So 'A%' matches names starting with 'A'. '%a' matches names ending with 'a'. '%ar%' matches names containing 'ar' anywhere.

Result

Queries return rows matching the flexible pattern, e.g., '%ar%' matches 'Albert' and 'Charlie'.

Knowing how % works helps you build powerful text searches that find partial matches.

4

Intermediate

Using _ Wildcard for Single Character

🤔Before reading on: does '_' match zero, one, or multiple characters? Commit to your answer.

Concept: _ matches exactly one character in the pattern.

The _ wildcard matches exactly one character. For example, 'A_ice' matches 'Alice' but not 'Alicia'. 'B_b' matches 'Bob' but not 'Bobby'.

Result

Queries return rows where the pattern fits exactly with one character replaced by any single character.

Understanding _ lets you search for text with specific character positions unknown.

5

Intermediate

Combining Wildcards for Complex Patterns

🤔Before reading on: can you combine % and _ in one pattern? Predict what 'A_%e' matches.

Concept: You can mix % and _ to create detailed search patterns.

For example, 'A_%e' matches names starting with 'A', followed by any characters, then ending with 'e'. It matches 'Alice' but not 'Alicia'.

Result

Queries return rows matching the combined pattern, allowing precise control over text matching.

Combining wildcards gives you fine-grained search power beyond simple starts or ends with.

6

Advanced

Case Sensitivity and Collation Effects

🤔Before reading on: do you think LIKE is always case sensitive? Commit to your answer.

Concept: LIKE's case sensitivity depends on the database and its settings.

In some databases, LIKE is case-insensitive by default (e.g., MySQL), while in others (e.g., PostgreSQL) it is case-sensitive. Collation settings affect this behavior. To do case-insensitive searches in case-sensitive DBs, you may use ILIKE or convert text to lower case.

Result

Understanding this avoids unexpected missing or extra results due to case differences.

Knowing how case sensitivity works prevents bugs in text searches and helps write portable queries.

7

Expert

Performance Implications of LIKE Patterns

🤔Before reading on: do you think all LIKE patterns use indexes equally well? Commit to your answer.

Concept: LIKE patterns with leading wildcards prevent index use, slowing queries.

If the pattern starts with %, the database cannot use indexes efficiently and must scan all rows. Patterns like 'abc%' can use indexes. This affects query speed on large tables. Experts design patterns and indexes carefully to balance flexibility and performance.

Result

Queries with leading % are slower; avoiding them or using full-text search can improve speed.

Understanding index use with LIKE helps optimize database performance in real applications.

Under the Hood

When a WHERE with LIKE runs, the database engine compares each row's column value to the pattern. It interprets % as any sequence of characters and _ as any single character. If the pattern starts with fixed characters, the engine can use indexes to quickly find matches. Otherwise, it scans rows one by one. The engine uses collation rules to decide if matching is case-sensitive or not.

Why designed this way?

LIKE was designed to provide simple, flexible text searching without complex regular expressions. Using % and _ as wildcards keeps the syntax easy to learn and use. Index usage depends on pattern structure to balance speed and flexibility. More complex pattern matching was left to other tools to keep SQL simple and fast for common cases.

┌───────────────┐
│ SQL Query     │
│ WHERE LIKE    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Pattern Match │
│ Engine        │
└──────┬────────┘
       │
       ▼
┌───────────────┐        ┌───────────────┐
│ Index Used?   │ ───No─▶│ Full Table    │
│ (pattern start)│       │ Scan          │
└──────┬────────┘        └───────────────┘
       │Yes
       ▼
┌───────────────┐
│ Index Lookup  │
│ for Matches   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LIKE 'A%' match names containing 'A' anywhere or only at the start? Commit to your answer.

Common Belief: LIKE 'A%' matches any name containing 'A' anywhere.

Reality: LIKE 'A%' matches only names starting with 'A'. To match anywhere, use '%A%'.

Why it matters: Misunderstanding this leads to missing expected results or getting wrong data.

Quick: Is LIKE case-insensitive in all databases? Commit to your answer.

Common Belief: LIKE is always case-insensitive.

Reality: LIKE's case sensitivity depends on the database and collation settings; some treat it as case-sensitive.

Why it matters: Assuming case-insensitivity can cause queries to miss matches or behave inconsistently across systems.

Quick: Does using % at the start of a pattern allow the database to use indexes? Commit to your answer.

Common Belief: Using % anywhere in LIKE pattern still uses indexes efficiently.

Reality: Leading % disables index use, causing slow full table scans.

Why it matters: Ignoring this causes performance problems on large datasets.

Quick: Does _ match zero or one character? Commit to your answer.

Common Belief: _ matches zero or one character.

Reality: _ matches exactly one character, no more, no less.

Why it matters: Misusing _ leads to unexpected matches or misses in search results.

Expert Zone

1

LIKE patterns can be combined with ESCAPE clauses to search for literal % or _ characters.

2

Different databases have subtle differences in LIKE behavior, such as Unicode support and collation sensitivity.

3

Using full-text search or regular expressions can be more efficient or powerful than LIKE for complex text queries.

When NOT to use

Avoid LIKE when searching large text fields with leading wildcards or complex patterns; use full-text search or regex functions instead. For numeric or date filtering, use direct comparisons rather than LIKE.

Production Patterns

In production, LIKE is often used for simple user search inputs like 'starts with' or 'contains' filters. Indexes are designed to support common patterns without leading wildcards. For complex search, systems combine LIKE with full-text indexes or external search engines.

Connections

Regular Expressions

LIKE is a simpler form of pattern matching compared to regular expressions.

Understanding LIKE helps grasp the basics of pattern matching before moving to the more powerful but complex regular expressions.

Full-Text Search

Full-text search builds on pattern matching concepts but adds indexing and ranking for large text data.

Knowing LIKE's limitations clarifies why full-text search is needed for efficient, advanced text queries.

Human Language Search

LIKE pattern matching relates to how people search for words or phrases with partial knowledge.

Recognizing this connection helps design user-friendly search features that match human expectations.

Common Pitfalls

#1 Using LIKE with a leading % causes slow queries on large tables.

Wrong approach:

SELECT * FROM Users WHERE Name LIKE '%son';

Correct approach:

SELECT * FROM Users WHERE Name LIKE 'Son%';

Root cause:

Leading % disables index use, forcing full table scans and slowing performance.

#2 Assuming LIKE is case-insensitive and missing matches.

Wrong approach:

SELECT * FROM Users WHERE Name LIKE 'alice'; -- misses 'Alice' if case-sensitive

Correct approach:

SELECT * FROM Users WHERE LOWER(Name) LIKE 'alice';

Root cause:

Database collation or LIKE behavior may be case-sensitive, requiring explicit case handling.

#3 Using _ wildcard expecting it to match zero characters.

Wrong approach:

SELECT * FROM Users WHERE Name LIKE 'Al_ce'; -- misses 'Alice' if expecting _ to be optional

Correct approach:

SELECT * FROM Users WHERE Name LIKE 'Alice';

Root cause:

_ matches exactly one character, not zero, so optional characters need different patterns.

Key Takeaways

The WHERE clause with LIKE lets you filter rows by matching text patterns using % and _ wildcards.

% matches any number of characters, while _ matches exactly one character in the pattern.

LIKE behavior, especially case sensitivity and index use, depends on the database system and settings.

Leading % in patterns disables index use, causing slower queries on large tables.

Understanding LIKE is essential before moving to advanced text search methods like regular expressions or full-text search.

Practice

1. Which SQL clause is used to filter rows based on a pattern in a text column?
SELECT * FROM employees WHERE name LIKE 'A%';

easy

A. GROUP BY

B. ORDER BY

C. WHERE with LIKE

D. JOIN

Solution

1. Step 1: Understand the purpose of LIKE

   The LIKE keyword is used to match text patterns in SQL queries.

2. Step 2: Identify the filtering clause

   The WHERE clause filters rows, and combined with LIKE, it filters rows matching a pattern.

3. Final Answer:

   WHERE with LIKE -> Option C

4. Quick Check:

   WHERE with LIKE = B [OK]

Hint: LIKE is always used with WHERE to filter text patterns [OK]

Common Mistakes:

• Confusing LIKE with GROUP BY
• Using LIKE without WHERE
• Thinking ORDER BY filters rows

2. Which of the following is the correct syntax to find names starting with 'Jo' in a table called users?

easy

A. SELECT * FROM users WHERE name LIKE '%Jo';

B. SELECT * FROM users WHERE name LIKE 'Jo%';

C. SELECT * FROM users WHERE name LIKE '_Jo%';

D. SELECT * FROM users WHERE name LIKE 'Jo_';

Solution

1. Step 1: Understand the pattern for names starting with 'Jo'

   Names starting with 'Jo' means 'Jo' followed by any characters, so pattern is 'Jo%'.

2. Step 2: Match the correct syntax

   SELECT * FROM users WHERE name LIKE 'Jo%'; uses LIKE 'Jo%' which matches names starting with 'Jo'. Others do not match this pattern.

3. Final Answer:

   SELECT * FROM users WHERE name LIKE 'Jo%'; -> Option B

4. Quick Check:

   Start with 'Jo' = LIKE 'Jo%' [OK]

Hint: Use % after text to match any ending characters [OK]

Common Mistakes:

• Using % before text to match start
• Using _ instead of %
• Misplacing wildcards

3. Given the table products with a column product_code, what rows will this query return?

SELECT * FROM products WHERE product_code LIKE '_A%';

medium

A. Rows where product_code starts with 'A'

B. Rows where product_code ends with 'A'

C. Rows where product_code contains 'A' anywhere

D. Rows where product_code has 'A' as the second character

Solution

1. Step 1: Understand the pattern '_A%'

   The underscore (_) matches exactly one character, so the first character can be anything. 'A' is the second character. '%' means any characters after.

2. Step 2: Interpret the query result

   The query returns rows where the second character of product_code is 'A', regardless of the first character or what follows.

3. Final Answer:

   Rows where product_code has 'A' as the second character -> Option D

4. Quick Check:

   _A% means second char is 'A' [OK]

Hint: Use _ for single char, % for any chars [OK]

Common Mistakes:

• Thinking _ matches zero or multiple characters
• Confusing position of 'A' in pattern
• Assuming LIKE matches anywhere without wildcards

4. Identify the error in this SQL query:

SELECT * FROM customers WHERE city LIKE '%York_';

It should find cities ending with 'York' followed by exactly one character.

medium

A. The pattern is correct; no error

B. The underscore should be before 'York' like '_%York'

C. The pattern should be '%York%' to match any after 'York'

D. The underscore should be after 'York' but without % like '%York_'

Solution

1. Step 1: Analyze the pattern '%York_'

   The % matches any characters before 'York', then 'York' literal, then _ matches exactly one character after.

2. Step 2: Check if it matches cities ending with 'York' plus one char

   This pattern correctly matches cities ending with 'York' plus exactly one character, so the query is correct.

3. Final Answer:

   The pattern is correct; no error -> Option A

4. Quick Check:

   '%York_' matches 'York' + 1 char [OK]

Hint: Check if _ and % are placed as intended [OK]

Common Mistakes:

• Thinking _ matches multiple characters
• Confusing placement of % and _
• Assuming pattern must end with %

5. You want to find all employees whose email ends with '.com' but have exactly one character before '.com'. Which query is correct?

hard

A. SELECT * FROM employees WHERE email LIKE '%_.com';

B. SELECT * FROM employees WHERE email LIKE '%_.com%';

C. SELECT * FROM employees WHERE email LIKE '%.__.com';

D. SELECT * FROM employees WHERE email LIKE '%.com';

Solution

1. Step 1: Understand the requirement

   The email should end with '.com' and have exactly one character before '.com'.

2. Step 2: Analyze each pattern

   SELECT * FROM employees WHERE email LIKE '%.com'; matches any email ending with '.com' but no restriction on characters before.
   SELECT * FROM employees WHERE email LIKE '%_.com'; uses '%_.com' meaning any characters, then exactly one character, then '.com' at the end.
   SELECT * FROM employees WHERE email LIKE '%.__.com'; expects two characters before '.com' which is incorrect.
   SELECT * FROM employees WHERE email LIKE '%_.com%'; allows any characters after '.com' which is incorrect for ending.

3. Final Answer:

   SELECT * FROM employees WHERE email LIKE '%_.com'; -> Option A

4. Quick Check:

   Use _ before '.com' for exactly one char [OK]

Hint: Use _ before '.com' to match exactly one char [OK]

Common Mistakes:

• Using % instead of _ for single character
• Placing % after '.com' allowing extra chars
• Miscounting characters before '.com'