LIKE pattern matching in MySQL - Deep Dive

Overview - LIKE pattern matching

What is it?

LIKE pattern matching is a way to search for text in a database that fits a certain pattern. It lets you find rows where a column's value matches a pattern with special symbols. These symbols can stand for any characters or a set of characters. This helps when you don't know the exact text but want to find similar or related entries.

Why it matters

Without LIKE pattern matching, searching for partial or flexible text in databases would be very hard and slow. You would have to know the exact text to find anything. This would make tasks like finding names starting with 'A' or emails containing 'gmail' impossible or very inefficient. LIKE makes searching more natural and powerful, saving time and effort.

Where it fits

Before learning LIKE, you should understand basic SQL SELECT queries and how to filter data with WHERE clauses. After mastering LIKE, you can explore more advanced text searching methods like regular expressions or full-text search for complex patterns.

Mental Model

Core Idea

LIKE pattern matching lets you find text in a database by using simple wildcards to represent unknown characters.

Think of it like...

It's like using a crossword puzzle clue with blanks: you know some letters but use blanks for unknown ones to find matching words.

Column Value Examples
─────────────────────────────
| 'apple'                 |
| 'application'           |
| 'banana'                |
| 'grape'                 |
─────────────────────────────

Query: WHERE name LIKE 'app%'
Matches: 'apple', 'application'

Symbols:
% = any number of characters
_ = exactly one character

Build-Up - 7 Steps

1

Foundation

Basic LIKE syntax and wildcards

Concept: Introduces the LIKE keyword and its two main wildcard symbols: % and _.

In SQL, LIKE is used in a WHERE clause to filter rows by matching text patterns. The % symbol matches zero or more characters, and the _ symbol matches exactly one character. For example, WHERE name LIKE 'a%' finds names starting with 'a'.

Result

You can write queries that find rows with text starting, ending, or containing certain letters or patterns.

Understanding the two wildcards % and _ is key to using LIKE effectively for flexible text searches.

2

Foundation

Using LIKE with different pattern positions

Concept: Shows how placing % and _ in different parts of the pattern changes the search behavior.

Patterns can start with %, end with %, or have % in the middle. For example, '%cat' finds text ending with 'cat', 'cat%' finds text starting with 'cat', and '%cat%' finds text containing 'cat' anywhere. Using _ lets you match a single unknown character, like 'c_t' matches 'cat' or 'cut'.

Result

You can target text that starts, ends, or contains certain sequences, or has specific character lengths.

Positioning wildcards changes what text matches, giving you precise control over pattern searches.

3

Intermediate

Escaping wildcard characters in patterns

🤔Before reading on: do you think you can search for a literal % character without special syntax? Commit to yes or no.

Concept: Explains how to search for actual % or _ characters by escaping them.

Since % and _ are special in LIKE patterns, to find them literally you must escape them using a backslash \ or the ESCAPE keyword. For example, WHERE name LIKE '%\%%' ESCAPE '\' finds text containing a literal percent sign.

Result

You can search for text that includes the wildcard symbols themselves, not just their pattern meaning.

Knowing how to escape wildcards prevents errors and lets you find exact text including special characters.

4

Intermediate

Case sensitivity and collation effects

🤔Before reading on: do you think LIKE searches are always case-sensitive? Commit to yes or no.

Concept: Introduces how case sensitivity depends on database collation settings and affects LIKE results.

In MySQL, whether LIKE is case-sensitive depends on the column's collation. For example, utf8_general_ci is case-insensitive, so 'A%' matches 'apple' and 'Apple'. Binary collations make LIKE case-sensitive. You can also use COLLATE to force case sensitivity or insensitivity.

Result

You understand why some LIKE queries match text regardless of case, while others do not.

Recognizing collation's role helps avoid confusion and ensures your pattern searches behave as expected.

5

Intermediate

Combining LIKE with other SQL filters

Concept: Shows how LIKE works with AND, OR, and NOT to build complex search conditions.

You can combine LIKE with other conditions. For example, WHERE name LIKE 'a%' AND city = 'Paris' finds names starting with 'a' in Paris. Using NOT LIKE excludes matches. OR lets you search multiple patterns, like WHERE name LIKE 'a%' OR name LIKE 'b%'.

Result

You can create powerful filters that mix pattern matching with other criteria.

Combining LIKE with logical operators expands your ability to find exactly the data you want.

6

Advanced

Performance considerations with LIKE patterns

🤔Before reading on: do you think LIKE '%text' queries are fast or slow? Commit to fast or slow.

Concept: Explains how pattern placement affects query speed and indexing.

LIKE queries starting with % (e.g., '%text') cannot use indexes efficiently, causing full table scans and slow performance on large tables. Patterns starting without % (e.g., 'text%') can use indexes and run faster. Understanding this helps design better queries and database schemas.

Result

You can write LIKE queries that perform well and avoid slow searches.

Knowing how pattern placement affects indexing prevents performance problems in real applications.

7

Expert

Limitations and alternatives to LIKE matching

🤔Before reading on: do you think LIKE can handle complex patterns like multiple optional parts or repetitions? Commit to yes or no.

Concept: Discusses the limits of LIKE and introduces more powerful text search options.

LIKE supports only simple wildcards and cannot express complex patterns like repetitions, optional parts, or character classes. For advanced needs, MySQL offers REGEXP for regular expressions and FULLTEXT indexes for natural language search. Choosing the right tool depends on your search complexity and performance needs.

Result

You know when to use LIKE and when to switch to more advanced search methods.

Understanding LIKE's limits helps you pick the best tool for your text search problems and avoid frustration.

Under the Hood

When you run a LIKE query, MySQL compares each row's text value against the pattern. It interprets % as any sequence of characters and _ as any single character. Internally, it scans the text character by character to check if the pattern fits. If the pattern starts with a fixed string (no leading %), MySQL can use indexes to quickly find matches. Otherwise, it scans all rows, which is slower.

Why designed this way?

LIKE was designed as a simple, easy-to-use pattern matching tool for common text searches. It uses % and _ as intuitive wildcards to cover most basic needs without the complexity of full regular expressions. This simplicity makes it accessible for beginners and efficient for many cases. More complex searches were left to REGEXP and full-text search to keep LIKE lightweight.

┌─────────────┐
│   Query     │
│ WHERE col   │
│ LIKE 'a%'   │
└─────┬───────┘
      │ Uses index if pattern
      │ starts with fixed text
      ▼
┌─────────────┐
│ Index Scan  │
│ (fast)      │
└─────┬───────┘
      │
      │ If pattern starts with %
      ▼
┌─────────────┐
│ Full Table  │
│ Scan (slow) │
└─────┬───────┘
      │
      ▼
┌─────────────┐
│ Pattern     │
│ Matching    │
│ Engine      │
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LIKE 'A%' match 'apple'? Commit to yes or no.

Common Belief: LIKE is always case-sensitive, so 'A%' won't match 'apple'.

Reality: LIKE's case sensitivity depends on the column's collation. Many collations are case-insensitive, so 'A%' matches 'apple'.

Why it matters: Assuming case sensitivity can cause missed matches or unexpected results in queries.

Quick: Can LIKE '%text' use an index to speed up search? Commit to yes or no.

Common Belief: LIKE queries with % anywhere can always use indexes efficiently.

Reality: If the pattern starts with %, indexes cannot be used, causing slow full scans.

Why it matters: Ignoring this leads to slow queries on large tables and poor application performance.

Quick: Does LIKE support complex patterns like 'a(b|c)*d'? Commit to yes or no.

Common Belief: LIKE supports complex pattern matching like regular expressions.

Reality: LIKE only supports simple wildcards % and _. Complex patterns require REGEXP or other tools.

Why it matters: Expecting complex matching from LIKE causes confusion and incorrect query design.

Quick: Can you search for a literal % character in LIKE without escaping? Commit to yes or no.

Common Belief: You can just write % in the pattern to find a literal percent sign.

Reality: You must escape % to search for it literally, otherwise it acts as a wildcard.

Why it matters: Not escaping leads to wrong matches or no matches when searching for special characters.

Expert Zone

1

LIKE performance depends heavily on pattern structure and underlying collation, which many overlook until scaling issues arise.

2

Escaping wildcards is often forgotten, causing subtle bugs when searching for text containing % or _ characters.

3

Combining LIKE with COLLATE can fine-tune case sensitivity per query without changing database schema.

When NOT to use

Avoid LIKE when you need complex pattern matching like repetitions, optional groups, or character classes. Use REGEXP for regex support or FULLTEXT indexes for natural language search instead.

Production Patterns

In production, LIKE is often used for simple filters like prefix searches or partial matches on small to medium datasets. For large datasets, queries are optimized to avoid leading % wildcards or replaced with full-text search for better performance.

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.

Indexing in Databases

LIKE pattern placement affects whether database indexes can be used.

Knowing how LIKE interacts with indexes helps optimize query performance and database design.

Search Algorithms

LIKE pattern matching is a basic form of substring search algorithm.

Recognizing LIKE as a substring search connects database queries to fundamental computer science search techniques.

Common Pitfalls

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

Wrong approach:

SELECT * FROM users WHERE name LIKE '%john%';

Correct approach:

SELECT * FROM users WHERE name LIKE 'john%';

Root cause:

Leading % prevents use of indexes, forcing full table scans and slow performance.

#2 Not escaping wildcard characters when searching for them literally.

Wrong approach:

SELECT * FROM products WHERE code LIKE '%50%'; -- tries to find literal '%'

Correct approach:

SELECT * FROM products WHERE code LIKE '%\%%' ESCAPE '\';

Root cause:

Forgetting that % and _ are special in LIKE patterns leads to incorrect matches.

#3 Assuming LIKE is case-sensitive regardless of collation.

Wrong approach:

SELECT * FROM employees WHERE name LIKE 'A%'; -- expects only names starting with uppercase A

Correct approach:

SELECT * FROM employees WHERE name LIKE 'A%' COLLATE utf8_bin; -- forces case sensitivity

Root cause:

Not understanding collation effects causes unexpected query results.

Key Takeaways

LIKE pattern matching uses % and _ as wildcards to find text that fits simple patterns.

The position of wildcards in the pattern affects both what matches and query performance.

Case sensitivity in LIKE depends on the database's collation settings, not the keyword itself.

Escaping wildcard characters is necessary to search for them literally in text.

For complex text searches, LIKE is limited; use REGEXP or full-text search instead.