SQL Topics
LIKE Operator
title: LIKE Operator
In real-world database applications, users often need to search for data based on patterns rather than exact values.
For example:
- A company wants employees whose names start with "A".
- A school wants students whose names end with "Kumar".
- An e-commerce website wants products containing the word "Phone".
- A bank wants customers whose email addresses belong to a specific domain.
Using the equals (=) operator is not suitable for these situations because exact matches are required.
To perform flexible text searches, SQL provides the LIKE Operator.
The LIKE operator is used to search for specific patterns within character data. It works with wildcard characters to match portions of text rather than complete values.
Because searching and filtering text data is extremely common, LIKE is one of the most frequently used SQL operators.
What is the LIKE Operator?
The LIKE operator is used to search for a specified pattern in a column.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 2 | Rohan |
Only names beginning with the letter R are returned.
Why is LIKE Important?
Consider a Customers table:
| CustomerID | Name |
|---|---|
| 1 | Rahul |
| 2 | Rohan |
| 3 | Amit |
| 4 | Priya |
Suppose we want customers whose names start with "R".
Without LIKE:
WHERE Name = 'Rahul'
OR Name = 'Rohan'This is impractical when many possible matches exist.
With LIKE:
WHERE Name LIKE 'R%'The query becomes shorter, cleaner, and more flexible.
Basic LIKE Syntax
The general syntax is:
SELECT ColumnName
FROM TableName
WHERE ColumnName LIKE Pattern;Example:
SELECT *
FROM Students
WHERE Name LIKE 'A%';Returns names beginning with "A".
Understanding the Syntax
Example:
SELECT *
FROM Customers
WHERE Name LIKE 'P%';SELECT
Retrieves data.
FROM
Specifies the table.
WHERE
Applies filtering.
LIKE
Performs pattern matching.
'P%'
Pattern to search.
Only matching records are returned.
Wildcards in SQL
The LIKE operator becomes powerful because of wildcard characters.
The two most commonly used wildcards are:
| Wildcard | Meaning |
|---|---|
| % | Any number of characters |
| _ | Exactly one character |
These wildcards allow flexible pattern matching.
Percentage (%) Wildcard
The % wildcard represents zero, one, or many characters.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Matches:
Rahul
Rohan
Rakesh
RajAll names start with R.
Names Ending with a Specific Letter
Example:
SELECT *
FROM Students
WHERE Name LIKE '%a';Matches:
Neha
PriyaThe name must end with "a".
Searching for a Word Anywhere
Example:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Matches:
iPhone
Phone Case
SmartPhoneThe word "Phone" can appear anywhere.
Searching for Exact Character Length
The underscore (_) wildcard represents exactly one character.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R____';Matches:
Rahul
RakesEach matching name contains exactly five characters.
Using Multiple Wildcards
Example:
SELECT *
FROM Customers
WHERE Name LIKE 'A%h';Matches:
Aakash
AshishThe name:
- Starts with A
- Ends with h
Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100)
);Insert data:
INSERT INTO Students VALUES
(1, 'Rahul'),
(2, 'Rohan'),
(3, 'Amit'),
(4, 'Priya'),
(5, 'Neha');Finding Names Beginning with R
Query:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 2 | Rohan |
Finding Names Ending with a
Query:
SELECT *
FROM Students
WHERE Name LIKE '%a';Result:
| StudentID | Name |
|---|---|
| 4 | Priya |
| 5 | Neha |
Finding Names Containing "it"
Query:
SELECT *
FROM Students
WHERE Name LIKE '%it%';Result:
| StudentID | Name |
|---|---|
| 3 | Amit |
The text can appear anywhere in the string.
LIKE with Numbers
Although primarily used with text, LIKE can sometimes be applied to numeric values converted to strings.
Example:
SELECT *
FROM Orders
WHERE OrderID LIKE '10%';Matches:
1001
1020
1055This depends on the database system.
LIKE with Email Addresses
Example:
SELECT *
FROM Customers
WHERE Email LIKE '%@gmail.com';Result:
Only Gmail addresses are returned.
Useful in customer analytics.
LIKE with Multiple Conditions
Example:
SELECT *
FROM Employees
WHERE Name LIKE 'R%'
AND Department = 'IT';Process:
Find Names Starting with R
ā
Check Department
ā
Return Matching RecordsLIKE with ORDER BY
Example:
SELECT *
FROM Customers
WHERE Name LIKE 'A%'
ORDER BY Name;Result:
Matching records are sorted alphabetically.
Real-World Example
Imagine an e-commerce platform.
Products table:
| ProductID | ProductName |
|---|---|
| 1 | Smartphone |
| 2 | Laptop |
| 3 | Phone Charger |
| 4 | Headphones |
Customer searches:
PhoneQuery:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Result:
| ProductName |
|---|
| ------------- |
| Smartphone |
| Phone Charger |
| Headphones |
This forms the basis of search functionality.
LIKE vs =
Using equals:
SELECT *
FROM Students
WHERE Name = 'Rahul';Returns:
Only RahulUsing LIKE:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Returns:
Rahul
Rohan
Rakesh
RajLIKE supports pattern matching while = requires exact matches.
Common Errors
Forgetting Quotes
Wrong:
WHERE Name LIKE R%Correct:
WHERE Name LIKE 'R%'Confusing % and _
Example:
_matches exactly one character.
Example:
%matches any number of characters.
Expecting Case-Insensitive Results
Some databases treat:
Rahul
rahuldifferently.
Behavior depends on database collation settings.
Using LIKE for Numeric Calculations
LIKE is intended for pattern matching, not arithmetic operations.
Best Practices
Use LIKE for Text Searches
Ideal for:
- Names
- Emails
- Product names
- Addresses
Use Wildcards Carefully
Leading wildcards:
'%text'can reduce performance on large datasets.
Combine with Other Conditions
Example:
WHERE Name LIKE 'R%'
AND City = 'Delhi'Use Indexes When Possible
Pattern searches can become expensive on large tables.
Test Search Patterns
Verify that the pattern matches the expected records.
Common Interview Questions
What is the purpose of LIKE?
LIKE is used to search for patterns within text values.
What does % represent?
The % wildcard represents zero or more characters.
What does _ represent?
The _ wildcard represents exactly one character.
What is the difference between LIKE and =?
LIKE performs pattern matching, while = requires exact matches.
Summary
The LIKE operator is a powerful SQL feature used to search for patterns in text data. It enables flexible filtering and forms the foundation of many search functionalities in modern applications.
In this lesson, you learned:
- What LIKE is
- Why it is important
- Wildcards (% and _)
- Pattern matching
- LIKE with emails
- LIKE with multiple conditions
- LIKE vs =
- Common mistakes
- Best practices
Understanding the LIKE operator is essential because searching text data efficiently is one of the most common requirements in database applications.
Next Step
Continue to the next lesson:
Wildcard Characters ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for LIKE Operator.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, like
Related SQL Topics