AI Reading
Quick summary of this article
Chapter 5: Pattern Matching, LIKE & Wildcards
AI Reading
Quick summary of Chapter 5
Text search is a cornerstone of modern web applications. In this chapter, you will master fuzzy pattern matching using the LIKE operator, understand wildcards (% and _), and learn how index sargability impacts search performance.
- Percent Wildcard (%): Match zero, one, or unlimited characters anywhere within a string.
- Underscore Wildcard (_): Match exactly one single character at an exact position.
- Pattern Recipes: Build prefix searches, suffix searches, substring finders, and length-constrained filters.
- Index Sargability: Understand why prefix searches (LIKE ‘A%’) use B-Tree indexes while substring searches (LIKE ‘%A%’) scan full tables.
- Escaping Characters: Search for literal percent signs and underscores using the ESCAPE clause.
Welcome to Chapter 5. When building search bars, autocomplete filters, or data reporting tools, users rarely know the exact text they are looking for. The LIKE operator allows you to perform pattern matching on string columns using SQL wildcard characters.
In this chapter, we will master wildcard expressions, study index performance implications, and explore practical string searching recipes.
🎯 5.1 The Two Fundamental Wildcards
%(Percent sign): Matches zero, one, or multiple characters._(Underscore): Matches exactly one single character.
📋 5.2 Master Wildcards Reference Table
| Pattern Syntax | Pattern Meaning | Matching Example | Non-Matching Example |
|---|---|---|---|
WHERE first_name LIKE 'S%' |
Starts with the letter “S” | Steven, Shelli, Sigal |
Lex (starts with L) |
WHERE first_name LIKE '%a' |
Ends with the letter “a” | Neena, Diana, Laura |
Steven (ends with n) |
WHERE first_name LIKE '%an%' |
Contains “an” anywhere | Daniel, Nancy, Shanta |
Bruce (no ‘an’) |
WHERE first_name LIKE '_r%' |
Has “r” in the 2nd position | Bruce, Irene |
Steven (2nd letter is t) |
WHERE first_name LIKE 'a_%_%' |
Starts with “a” and length >= 3 | Adam (4 chars), Alexander |
Al (only 2 chars) |
WHERE first_name LIKE 'a%o' |
Starts with “a” and ends with “o” | Antonio |
Adam (ends with m) |
💻 5.3 Practical Query Examples
A. Prefix Search (First name starts with ‘S’)
SELECT first_name, last_name, job_id, salary
FROM employees
WHERE UPPER(first_name) LIKE 'S%';B. Substring Search (First name contains ‘an’)
SELECT first_name, last_name, job_id
FROM employees
WHERE LOWER(first_name) LIKE '%an%';⚠ 5.4 Index Performance & Sargability
One of the most important concepts in database optimization is Sargability (Search Argument Able):
WHERE name LIKE 'Stev%': SARGABLE! Because the prefix is known, MySQL can use a standard B-Tree index onnameto jump directly to the matching range.WHERE name LIKE '%ing'or'%te%': NON-SARGABLE! Because the start of the string is unknown, MySQL cannot traverse the B-Tree index and must execute an expensive Full Table Scan, inspecting every row in the table.
🔎 5.5 Escaping Literal Wildcards
What if you want to find a literal discount like '10%' or an underscore in a username like 'john_doe'? You must escape the wildcard character using a backslash \ or an explicit ESCAPE clause:
-- Searching for a literal 10% string using default escape:
SELECT * FROM promotions WHERE promo_code LIKE '%10\%%';
-- Using a custom escape character:
SELECT * FROM users WHERE username LIKE '%!_%' ESCAPE '!';❓ Frequently Asked Questions (FAQ)
1. Is LIKE case-sensitive in MySQL?
By default, string comparisons in MySQL depend on the table column’s collation. Most default collations (like utf8mb4_0900_ai_ci or utf8mb4_general_ci) end in _ci, meaning Case-Insensitive. If you require case-sensitive searches, cast the column to binary: WHERE BINARY first_name LIKE 'S%'.
2. When should I use FULLTEXT indexes instead of LIKE ‘%term%’?
If you are searching across large text bodies, articles, or product descriptions, LIKE '%term%' will cause massive server slowdowns. Instead, create a MySQL FULLTEXT index and use MATCH(column) AGAINST('term' IN NATURAL LANGUAGE MODE).