How Does the LIKE Operator Work in SQL?
Learn the SQL LIKE operator with % and _ wildcards: starts-with, ends-with and contains patterns, case sensitivity, escaping, and performance tips.
Expected Interview Answer
The LIKE operator performs pattern matching on text in a WHERE clause, returning rows whose column value matches a pattern built from two wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character.
You write conditions like WHERE name LIKE 'A%' (starts with A), '%son' (ends with son), '%mit%' (contains mit), or '_at' (any single character followed by 'at'). Matching is case-sensitive or insensitive depending on the database and column collation; PostgreSQL adds ILIKE for explicit case-insensitive matching. To match a literal % or _, you escape it, e.g. LIKE '50\%' ESCAPE '\'. A leading % usually prevents an index from being used, so such patterns can be slow on large tables.
- Flexible partial and prefix text search
- Simple, readable syntax with just two wildcards
- Supported by virtually every SQL database
- Prefix patterns (abc%) can use an index
- Combines naturally with AND/OR and NOT LIKE
AI Mentor Explanation
LIKE is like scanning a squad list for players whose name fits a pattern. 'S%' finds every player starting with S, '%singh' finds everyone ending in Singh, and '_ohli' matches any single first letter before 'ohli'. The % stands in for any run of letters, the underscore for exactly one.
Step-by-Step Explanation
Step 1
Choose the column and pattern
Decide which text column to search and what shape the value should match.
Step 2
Place the wildcards
Use % for any run of characters and _ for exactly one character in the right positions.
Step 3
Write the WHERE clause
Add WHERE column LIKE 'pattern', e.g. WHERE email LIKE '%@gmail.com'.
Step 4
Handle case and literals
Use ILIKE or LOWER() for case-insensitive matches, and ESCAPE to match literal % or _.
Step 5
Watch performance
Avoid a leading % when possible, since it prevents normal index use on large tables.
What Interviewer Expects
- Correct meaning of % (many chars) and _ (one char)
- Example patterns for starts-with, ends-with and contains
- Awareness of case sensitivity and ILIKE
- How to escape literal wildcard characters
- That a leading % typically defeats indexes
Common Mistakes
- Swapping the roles of % and _
- Assuming _ matches any number of characters
- Expecting LIKE to be case-insensitive everywhere
- Forgetting a leading % blocks index usage
- Not escaping literal % or _ when searching for them
Best Answer (HR Friendly)
“The LIKE operator finds text that matches a pattern instead of an exact value. A percent sign stands for any number of characters and an underscore for a single character, so LIKE 'A%' finds everything starting with A — much like a search box that matches partial words.”
Code Example
-- names starting with 'A'
SELECT * FROM customers
WHERE name LIKE 'A%';
-- emails ending in @gmail.com
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';
-- exactly one char, then 'at'
SELECT * FROM words
WHERE term LIKE '_at';-- case-insensitive (PostgreSQL)
SELECT * FROM products
WHERE name ILIKE '%pro%';
-- match a literal percent sign
SELECT * FROM offers
WHERE label LIKE '50\%' ESCAPE '\';Follow-up Questions
- What is the difference between % and _ in LIKE?
- How do you make a LIKE search case-insensitive?
- How do you match a literal % or _ character?
- Why can a leading % make LIKE queries slow?
- When would you use full-text search instead of LIKE?
MCQ Practice
1. In LIKE, what does the underscore (_) match?
The _ wildcard matches exactly one character, whereas % matches any sequence of characters.
2. Which pattern finds values ending in 'son'?
A leading % matches any prefix, so '%son' returns values that end with 'son'.
3. Why can WHERE name LIKE '%abc' be slow on a large table?
A leading wildcard means the engine cannot use a B-tree index prefix, often forcing a full scan.
Flash Cards
What does % match in LIKE? — Any sequence of zero or more characters.
What does _ match in LIKE? — Exactly one character.
Case-insensitive LIKE in PostgreSQL? — Use ILIKE, e.g. name ILIKE '%pro%'.
How to match a literal % ? — Escape it: LIKE '50\%' ESCAPE '\'.