LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
It is mainly used when we do not know the complete value and want to find records based on a particular pattern.
What is LIKE Operator?
The LIKE operator is used with the WHERE clause to search for a specific pattern in character or string data.
LIKE is commonly used for searching names, cities, email addresses and other text values.
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';
Example Table
| StudentId | StudentName | City | Course |
|---|---|---|---|
| 101 | Rahul | Pune | .NET |
| 102 | Priya | Mumbai | Java |
| 103 | Amit | Nashik | Python |
| 104 | Neha | Pune | .NET |
| 105 | Rohan | Pune | Java |
% Wildcard
The % wildcard represents zero, one or multiple characters.
Starts With a Pattern
To find names that start with a particular character, place the character before the % wildcard.
SELECT *
FROM Students
WHERE StudentName LIKE 'R%';
This returns students whose names start with R.
Ends With a Pattern
To find names ending with a particular character, place the % wildcard before the character.
SELECT *
FROM Students
WHERE StudentName LIKE '%a';
This searches for names ending with the letter a.
Contains a Pattern
To search for a character or pattern anywhere in the value, use % on both sides.
SELECT *
FROM Students
WHERE StudentName LIKE '%an%';
This searches for names containing the pattern an.
_ Underscore Wildcard
The underscore _ wildcard represents exactly one character.
SELECT *
FROM Students
WHERE StudentName LIKE 'R_hul';
Here, the underscore represents one character between R and hul.
Multiple Underscores
Multiple underscore characters can be used when a fixed number of unknown characters are required.
SELECT *
FROM Students
WHERE StudentName LIKE '____';
This pattern matches values containing exactly four characters.
LIKE with WHERE
LIKE is normally used together with the WHERE clause.
SELECT StudentName, City
FROM Students
WHERE City LIKE 'P%';
This searches for cities beginning with the letter P.
LIKE with AND
Multiple LIKE conditions can be combined using AND.
SELECT *
FROM Students
WHERE StudentName LIKE 'R%'
AND City LIKE 'P%';
This searches for students whose name starts with R and whose city starts with P.
LIKE with OR
SELECT *
FROM Students
WHERE City LIKE 'P%'
OR City LIKE 'M%';
This searches for cities beginning with either P or M.
NOT LIKE
The NOT LIKE operator is used when we want values that do not match a specified pattern.
SELECT *
FROM Students
WHERE StudentName NOT LIKE 'R%';
This returns students whose names do not start with R.
Important
- LIKE is used for pattern matching.
- % represents zero or more characters.
- _ represents exactly one character.
- LIKE is commonly used with WHERE.
- NOT LIKE is used to exclude a matching pattern.
Summary
In this section, we learned how the LIKE operator is used for pattern matching in SQL Server. We covered the % and _ wildcards, starts-with, ends-with, contains, NOT LIKE, and combining LIKE with AND and OR.