LIKE Operator

LIKE Operator in MySQL

Introduction
Exact matches miss flexible text searches like partial names or categories.
The LIKE operator in MySQL performs pattern matching using wildcards.
It is widely used for:

• Partial text searches
• Name/email pattern matching
• Category filtering
• Flexible user input search

What is the LIKE Operator
LIKE compares string values using pattern matching with wildcards % and _.
Returns TRUE when the pattern matches anywhere in the string.

Basic Syntax

sql

SELECT column_name 

FROM table_name 

WHERE column_name LIKE 'pattern'; 

Example

sql

SELECT * FROM Students 

WHERE name LIKE 'A%'; 

Returns all students whose name starts with 'A'.

Wildcard % (Any Characters)
% matches zero or more characters anywhere.

sql

SELECT * FROM Students 

WHERE name LIKE '%kumar'; 

Matches 'Ramesh Kumar', 'Amit Kumar', etc.

Wildcard _ (Single Character)
_ matches exactly one character.

sql

SELECT * FROM Students 

WHERE name LIKE 'A_it'; 

Matches 'Amit', 'Anit', but not 'Amitt'.

LIKE Pattern Combinations
Multiple wildcards in one pattern.

sql

SELECT * FROM Students 

WHERE name LIKE 'R__esh'; 

Matches 'Ramesh', 'Raresh' (6 letters total).

LIKE with Multiple Conditions
Combine with AND/OR.

sql

SELECT * FROM Students 

WHERE name LIKE 'A%' AND city LIKE 'M%'; 

LIKE with NOT
Exclude pattern matches.

sql

SELECT * FROM Students 

WHERE name NOT LIKE '%kumar'; 

LIKE Case Sensitivity
Depends on collation (utf8mb4_general_ci = case-insensitive).

sql

SELECT * FROM Students 

WHERE name LIKE 'amit';  -- Matches 'Amit', 'AMIT.' 

LIKE with ESCAPE Clause
Search for literal % or _.

sql

SELECT * FROM Students 

WHERE address LIKE '%50\% rent' ESCAPE '\'; 

Matches addresses containing '50% rent'.

LIKE with ORDER BY
Pattern match, then sort.

sql

SELECT * FROM Students 

WHERE name LIKE 'S.%.' 

ORDER BY marks DESC; 

LIKE with GROUP BY
Group pattern-matched results.

sql

SELECT city, COUNT(*) 

FROM Students 

WHERE name LIKE '%sin.gh.' 

GROUP BY city; 

LIKE Performance Issues
Leading % kills index usage.

sql

-- SLOW (no index) 

WHERE name LIKE '%kumar'; 

 

-- FAST (uses index) 

WHERE name LIKE 'Kumar%'; 

LIKE with Full-Text Search Alternative
For large datasets, consider MATCH AGAINST.

sql

-- Instead of complex LIKE for many words 

SELECT * FROM articles 

WHERE MATCH(title, content) AGAINST('mysql tutorial'); 

Execution Order with LIKE
Evaluated in the WHERE clause after FROM.
FROM → WHERE (LIKE patterns) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• Avoid leading % (prevents index use)
• Index searchable columns
• Use FULLTEXT for complex searches
• LIMIT results early
• Consider REGEXP for complex patterns

Important Notes

• % = zero or more chars, _ = exactly one char
• Case sensitivity depends on collation
• NULL LIKE pattern = NULL
• ESCAPE needed for literal % _ characters
• MySQL LIKE is faster than REGEXP

Example Scenario
Find students with specific name patterns:

sql

SELECT name, city, marks 

FROM Students 

WHERE (name LIKE 'A%' OR name LIKE 'R%') 

  AND city LIKE 'Del.hi%.' 

  AND marks > 75 

ORDER BY marks DESC, name 

LIMIT 20;

Common Mistakes

• Leading % preventing index usage
• Forgetting ESCAPE for % _ literals
• Using LIKE for exact matches (= faster)
• Case sensitivity surprises
• Complex patterns are better with REGEXP

Key Points to Remember

• LIKE uses % (many chars) and _ (one char) wildcards
• Leading % prevents index optimisation
• NOT LIKE excludes pattern matches
• ESCAPE clause for literal wildcard chars
• Use FULLTEXT for complex text search
• Case sensitivity depends on table collation