SQL Notes
Learn how to use the SQL LIKE operator to search for patterns in text data, work with wildcards, perform flexible searches, and filter records efficiently.
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.
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 Raj
All names start with R.
Names Ending with a Specific Letter
Example:
SELECT *
FROM Students
WHERE Name LIKE '%a';Matches:
Neha Priya
The name must end with "a".
Searching for a Word Anywhere
Example:
SELECT *
FROM Products
WHERE ProductName LIKE '%Phone%';Matches:
iPhone Phone Case SmartPhone
The 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 Rakes
Each matching name contains exactly five characters.
Using Multiple Wildcards
Example:
SELECT *
FROM Customers
WHERE Name LIKE 'A%h';Matches:
Aakash Ashish
The 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 1055
This 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:
Phone
Query:
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 Rahul
Using LIKE:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Returns:
Rahul Rohan Rakesh Raj
LIKE 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 rahul
differently.
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, filtering, like, operator
Related SQL Complete Guide Topics