LIKE Operator and Wildcards in SQL: % and _ for Pattern Matching

Exact-match filtering with = works perfectly when you know precisely what you're looking for, but real searches are rarely that exact — a user searching for 'phone' should probably find 'smartphone' too. The LIKE operator, combined with the % and _ wildcards, gives SQL flexible text pattern matching for exactly this kind of partial, real-world search.

What Is the LIKE Operator?

LIKE is an operator used in a WHERE clause to match text against a pattern containing wildcard characters, rather than requiring an exact match. The % wildcard matches any sequence of zero or more characters, while the _ wildcard matches exactly one single character.

What You'll Learn

• Use the % wildcard to match any number of characters before, after, or around a pattern.
• Use the _ wildcard to match exactly one character in a specific position.
• Write common patterns: starts-with, ends-with, and contains searches.
• Understand the performance trade-offs of leading-wildcard LIKE patterns.

Key Terms to Know

• LIKE: An operator for pattern-based text matching using wildcards.
• % wildcard: Matches any sequence of zero or more characters.
• _ wildcard: Matches exactly one character.
• NOT LIKE: The inverse of LIKE, matching text that does not fit a given pattern.

The % Wildcard: Matching Any Number of Characters

WHERE product_name LIKE 'wireless%'; matches any value starting with 'wireless', followed by zero or more additional characters — 'wireless mouse', 'wireless earbuds', and even just 'wireless' itself would all match. Placing % at both ends, like LIKE '%phone%', matches any value that contains 'phone' anywhere within it, which is the classic 'contains' search pattern.

The _ Wildcard: Matching Exactly One Character

WHERE product_code LIKE 'A_01'; matches any 4-character value starting with 'A', followed by exactly one arbitrary character, then ending in '01' — 'AB01' and 'AX01' would both match, but 'ABC01' (5 characters) would not, since _ matches exactly one character, no more and no less.

This is especially useful for matching fixed-format codes where one specific position is variable but the overall length and surrounding characters are fixed.

NOT LIKE and Performance Considerations

WHERE product_name NOT LIKE '%refurbished%'; excludes any product whose name contains the word 'refurbished' anywhere within it, directly inverting the LIKE match.

A pattern starting with a leading % (like '%phone') is significantly slower on large tables, since MySQL generally can't use a standard index to jump directly to matching rows — it has to scan and check most or all rows individually. Patterns without a leading wildcard, like 'phone%', can often use an index far more efficiently. For serious full-text search needs at scale, MySQL's dedicated FULLTEXT indexes are a better fit than relying on LIKE with leading wildcards.

Visual Summary

Picture % as an accordion that can stretch to cover any number of letters, including zero, while _ is a single fixed-size tile that always covers exactly one letter, no more, no less. 'wireless%' is an accordion fixed at the start that can stretch as far as needed afterward. 'A_01' is a rigid template with one flexible tile in the second position and fixed letters everywhere else.

Common LIKE Patterns

Pattern	Matches	Example Match
'wireless%'	Starts with 'wireless'	'wireless mouse'
'%mouse'	Ends with 'mouse'	'wireless mouse'
'%phone%'	Contains 'phone' anywhere	'smartphone case'
'A_01'	4 characters: A, any 1 char, 0, 1	'AB01', 'AX01'

SQL Example

-- Starts-with search
SELECT product_name FROM products WHERE product_name LIKE 'wireless%';

-- Contains search
SELECT product_name FROM products WHERE product_name LIKE '%phone%';

-- Exactly one variable character in a fixed-format code
SELECT product_code FROM products WHERE product_code LIKE 'A_01';

-- Excluding a pattern with NOT LIKE
SELECT product_name FROM products WHERE product_name NOT LIKE '%refurbished%';

The first query finds products beginning with 'wireless'. The second finds any product whose name contains 'phone' anywhere at all. The third demonstrates the precise, single-character flexibility of the _ wildcard for fixed-format codes. The fourth shows NOT LIKE excluding refurbished items from a results list, a common real-world e-commerce filtering need.

Real-World Examples

• E-commerce search bars use LIKE '%searchterm%' style queries (or full-text search engines for larger catalogs) to support flexible product name matching.
• Customer support tools use LIKE to search ticket subjects or descriptions for keywords without requiring an exact match.
• Inventory systems use _ wildcards to validate or search fixed-format SKUs where only specific character positions vary.
• Email marketing platforms use NOT LIKE '%unsubscribed%' style filters (alongside more robust status columns) when cleaning up legacy free-text data.

Best Practices and Pro Tips

• Avoid leading-wildcard patterns like '%term' on large, frequently queried tables when possible — they can't use a standard index efficiently and will scan far more rows than necessary.
• For serious search functionality at scale (ranking, relevance, multi-word matching), reach for MySQL's FULLTEXT indexes or a dedicated search engine instead of relying purely on LIKE.
• Be explicit and deliberate about wildcard placement — a small misplaced % can turn an intended 'starts with' search into an unintended 'contains' search.

Common Mistakes to Avoid

• Forgetting to add % wildcards entirely, turning LIKE 'phone' into an exact match search, identical to using = and offering no pattern flexibility at all.
• Using a leading % wildcard on a large table without realizing the performance impact compared to a trailing-only wildcard pattern.
• Confusing % and _ — using _ when a flexible number of characters was actually intended, leading to unexpectedly narrow matches.
• Relying on LIKE for serious search relevance ranking, when a proper full-text search solution would serve the use case far better.

Interview Questions

Q1. What is the difference between the % and _ wildcards in SQL's LIKE operator?

% matches any sequence of zero or more characters, while _ matches exactly one single character, no more and no less.

Q2. Why is a leading-wildcard LIKE pattern like '%term' generally slower than a trailing-wildcard pattern like 'term%'?

A trailing wildcard allows MySQL to use a standard index to quickly locate matching rows starting with the known prefix. A leading wildcard prevents this, since the matching text could start anywhere, forcing MySQL to scan most or all rows individually.

Q3. How would you write a LIKE pattern to find product names containing the word 'pro' anywhere in the name?

WHERE product_name LIKE '%pro%'; using % on both sides of the search term to match it appearing anywhere within the value, not just at the start or end.

Q4. What does NOT LIKE do?

NOT LIKE returns rows whose value does not match the specified pattern, the exact logical inverse of LIKE, which returns rows whose value does match the pattern.

Practice MCQs

1. Which wildcard matches exactly one character?

1. %
2. _
3. *
4. ?

Answer: B. _

Explanation: The underscore (_) wildcard in SQL's LIKE operator matches exactly one character, no more and no less.

2. Which LIKE pattern would match 'smartphone' but not 'phone'?

1. '%phone%'
2. 'phone%'
3. '%phone'
4. '_phone'

Answer: B. 'phone%'

Explanation: 'phone%' requires the value to start with 'phone', which 'smartphone' does not (it ends with 'phone'), while '%phone' or '%phone%' would actually match both depending on position.

3. Why are leading-wildcard LIKE patterns generally slower on large tables?

1. They use more storage
2. They can't typically use a standard index efficiently, requiring a fuller scan
3. They are syntactically invalid
4. They only work on numeric columns

Answer: B. They can't typically use a standard index efficiently, requiring a fuller scan

Explanation: Since the matching text could begin anywhere, MySQL generally can't jump directly to matching rows using a standard index, unlike with a trailing-wildcard pattern.

Quick Revision Points

• % matches zero or more characters; _ matches exactly one character.
• Trailing-wildcard patterns ('term%') can use indexes efficiently; leading-wildcard patterns ('%term') generally cannot.
• NOT LIKE is the direct inverse of LIKE.
• For serious search needs at scale, FULLTEXT indexes are a better fit than LIKE.

Conclusion

• LIKE with % and _ gives SQL genuinely flexible, real-world text search capability.
• Wildcard placement directly affects both matching behavior and query performance.
• Knowing when to graduate from LIKE to a proper full-text search solution is a valuable practical judgment call.

The LIKE operator, paired with the % (any number of characters) and _ (exactly one character) wildcards, gives SQL flexible pattern-based text matching well beyond simple exact equality. Understanding wildcard placement — and the real performance cost of leading wildcards on large tables — turns LIKE from a quick trick into a properly understood tool. The next lesson covers IS NULL and IS NOT NULL, addressing the special handling NULL values require throughout SQL filtering.