Wildcards in MySQL
Introduction
Flexible pattern matching finds partial data without exact values.
Wildcards % and _ work with the LIKE operator for pattern-based searching.
They are widely used for:
- Partial name searches
- Code pattern matching
- Flexible user input filtering
What are Wildcards
Wildcards are special characters in LIKE patterns:
- % = zero or more characters
- _ = exactly one character
Basic Syntax
Example
sql
SELECT * FROM Students
WHERE name LIKE 'A%';
All names starting with 'A' (Amit, Anil, etc.).
% Wildcard (Zero or More Characters)
Matches any string (including empty).
sql
SELECT * FROM Students
WHERE name LIKE '%kumar'; -- Ramesh Kumar, Amit Kumar
SELECT * FROM Students
WHERE name LIKE 'k%'; -- All names starting with 'k'
_ Wildcard (Single Character)
Matches exactly one character only.
sql
SELECT * FROM Students
WHERE name LIKE 'A_it'; -- Amit, Anit (not Amitt)
SELECT * FROM Students
WHERE name LIKE 'R_m'; -- Ram, Rim (3-letter names)
Multiple Wildcards
Combine % and _ in patterns.
sql
SELECT * FROM Students
WHERE name LIKE 'Ra__h'; -- Rahul, Rakesh (5 letters)
SELECT * FROM Students
WHERE name LIKE 'A%sh'; -- Anish, Amarash
Wildcard with NOT LIKE
Exclude patterns.
sql
SELECT * FROM Students
WHERE name NOT LIKE 'A%'; -- Names not starting with A
ESCAPE Clause for Literal Wildcards
Search for actual % or _ characters.
sql
SELECT * FROM Students
WHERE notes LIKE '%50\% off%' ESCAPE '\';
Finds notes containing "50% off".
Wildcards with Multiple Conditions
Pattern + exact match.
sql
SELECT * FROM Students
WHERE name LIKE 'S%' AND city = 'Delhi';
Wildcards with ORDER BY
Pattern match, then sort.
sql
SELECT * FROM Students
WHERE name LIKE '%son.'
ORDER BY marks DESC;
Wildcards with GROUP BY
Pattern-based aggregation.
sql
SELECT LEFT(name,1) as initial, COUNT(*)
FROM Students
WHERE name LIKE 'A%' OR name LIKE 'R%'
GROUP BY LEFT(name,1);
Wildcards with JOIN
Pattern matching across tables.
sql
SELECT s.name, c.course_name
FROM Students s
JOIN Courses c ON s.course_id = c.id
WHERE s.name LIKE 'A%' AND c.duration > 6;
Performance Impact
Leading % prevents index usage.
sql
-- SLOW (full table scan)
WHERE name LIKE '%kumar';
-- FAST (index used)
WHERE name LIKE 'Kumar%';
Common Wildcard Patterns
sql
LIKE 'A%'; -- Starts with A
LIKE '%A'; -- Ends with A
LIKE '%A%'; -- Contains A
LIKE 'A_'; -- A + 1 char (Ab, Ax)
LIKE '_A_'; -- 3 chars, middle A
Execution Order with Wildcards
Evaluated in WHERE after FROM.
FROM → WHERE (LIKE wildcards) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- Avoid leading % (no index usage)
- Trailing % uses indexes
- Multiple wildcards = full scan
- Consider FULLTEXT for complex searches
Important Notes
- % matches zero characters too
- _ matches exactly one character only
- Case sensitivity = table collation
- ESCAPE needed for literal % _
- NULL LIKE pattern = NULL
Example Scenario
Find students with common name patterns:
sql
SELECT name, city, marks
FROM Students
WHERE (name LIKE 'A%' OR name LIKE 'R%' OR name LIKE '%kumar')
AND marks > 75
AND city NOT LIKE 'Remote.%'
ORDER BY marks DESC, name
LIMIT 50;
Common Mistakes
- Leading % killing performance
- _ matching more/fewer chars than expected
- Forgetting ESCAPE for % _ literals
- Using wildcards for exact matches
- Case sensitivity surprises
Key Points to Remember
- % = zero or more chars, _ = exactly one char
- Leading % prevents index usage
- ESCAPE clause for literal wildcards
- NOT LIKE excludes patterns
- Combine with exact matches for precision
- Performance varies by wildcard position