SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a pattern in a text column. Instead of searching for an exact value, LIKE allows searching based on partial matches — like finding all students whose name starts with 'R', or all email addresses from a specific domain.
Think of it like a basic search box — when typing a few letters, results that contain those letters appear.
The Reference Table
| StudentID | StudentName | City | |
|---|---|---|---|
| 1 | Ravi Sharma | Delhi | ravi@gmail.com |
| 2 | Priya Mehta | Mumbai | priya@yahoo.com |
| 3 | Arjun Nair | Chennai | arjun@gmail.com |
| 4 | Sneha Kapoor | Pune | sneha@outlook.com |
| 5 | Rohit Das | Kolkata | rohit@gmail.com |
Wildcard Characters
LIKE uses two special wildcard characters to build patterns:
| Wildcard | Meaning | Example Pattern | Matches |
|---|---|---|---|
% | Represents zero or more characters (any number of characters) | 'R%' | Ravi, Rohit, Ram, R, Raj... |
_ | Represents exactly one character | 'R_vi' | Ravi, Revi, Rovi (exactly 4 characters) |
Syntax
SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';Pattern Examples With % (Percent)
Example 1: Names That Start With 'R'
SELECT StudentName FROM Students
WHERE StudentName LIKE 'R%';Result: Ravi Sharma, Rohit Das
Example 2: Names That End With 'a'
SELECT StudentName FROM Students
WHERE StudentName LIKE '%a';Result: Ravi Sharma, Priya Mehta, Sneha Kapoor (ends with 'a')
Example 3: Names That Contain 'ar' Anywhere
SELECT StudentName FROM Students
WHERE StudentName LIKE '%ar%';Result: Ravi Sharma (contains 'ar' in 'Sharma'), Arjun Nair (contains 'ar' in 'Arjun')
Example 4: Finding All Gmail Users
SELECT StudentName, Email FROM Students
WHERE Email LIKE '%@gmail.com';Result:
| StudentName | |
|---|---|
| Ravi Sharma | ravi@gmail.com |
| Arjun Nair | arjun@gmail.com |
| Rohit Das | rohit@gmail.com |
Pattern Examples With _ (Underscore)
Example 5: Cities With Exactly 4 Characters
SELECT StudentName, City FROM Students
WHERE City LIKE '____';(Four underscores = exactly 4 characters)
Result: Pune (4 characters)
Example 6: Names With 'r' As the Second Character
SELECT StudentName FROM Students
WHERE StudentName LIKE '_r%';Result: Priya Mehta, Arjun Nair (both have 'r' as the second character)
Combining % and _ Together
SELECT StudentName FROM Students
WHERE StudentName LIKE 'R__%';This finds names that start with 'R' and have at least 3 characters total. Matches: Ravi Sharma, Rohit Das.
NOT LIKE
NOT LIKE finds all rows that do not match the pattern.
SELECT StudentName, Email FROM Students
WHERE Email NOT LIKE '%@gmail.com';Result: Priya Mehta (yahoo.com) and Sneha Kapoor (outlook.com) — neither uses Gmail.
Case Sensitivity
In most databases, LIKE is not case-sensitive for text columns by default in MySQL. This means LIKE 'r%' and LIKE 'R%' would return the same results in MySQL. However, PostgreSQL is case-sensitive by default — use ILIKE in PostgreSQL for case-insensitive matching.
Common LIKE Patterns Reference
| Pattern | What It Matches |
|---|---|
'A%' | Starts with A |
'%a' | Ends with a |
'%abc%' | Contains 'abc' anywhere |
'_a%' | Second character is 'a' |
'a_%_%' | Starts with 'a' and has at least 3 characters |
'a%z' | Starts with 'a' and ends with 'z' |
Key Points to Remember
%matches any number of characters (including zero characters)._matches exactly one character.- Patterns are enclosed in single quotes.
NOT LIKEreturns rows that do not match the pattern.LIKEis used only with text (string) columns — not with numbers or dates.
Summary
The LIKE operator enables pattern-based searching in SQL. Using the % and _ wildcards, flexible text searches can be performed — finding values that start with, end with, or contain a specific sequence of characters. It is commonly used in search features, email filtering, and name lookups.