SQL Server: Tips and Tricks - 2 (2017)
What is LIKE Logical Operator in SQL Server
LIKE logical operator is used when we want to return the row if operand matches a pattern. Like operator returns TRUE if the operand matches a pattern.
Sometime we need to perform pattern matching instead of equal or not equal. Like is used when we want to return the row if specific character string matches a specified pattern. Pattern can be combination of regular characters and wildcard characters.
To return the row back, regular characters must exactly match the characters specified in the character string. The wildcard characters can be matched with arbitrary parts of the character string.
Let's create dbo.Customer table and then create some real time examples
Create table dbo.Customer
(
Id int,
FName VARCHAR(50),
LName VARCHAR(50),
CountryShortName CHAR(2)
)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(5,'Robert','Ladson','US'),
(6,'Alice','John','US'),
(7,'Raza','M','US'),
(8,'Dita','M','US'),
(9,'Adita','M','US')
1) Using %
Let's say if we want to find all the rows where FName contains "i" in them. We can use below query
Select * From dbo.Customer
where FName like '%i%'
|
|
Noticed that by using % before and after "i", we are telling the query to find all rows in which FName has "i" character and does not matter what other characters are before and after "i".
2) Using _ (underscore)
The underscore can be used when we want to check single character that can be anything and provide the rest of the characters for our match. Let's say that if I want to find all rows where FName first character can be anything but rest of them should be "ita". I can use below query.
Select * From dbo.Customer
where FName like '_ita'
|
|
3) Using [ ] - Any single character within the specified rang [a-t] or set [abc]
Like operator with [ ] can be used when we want to have range. Let's say if I want to find all the rows where FName first character start with [a-f]. We can use below query.
Select * From dbo.Customer
where FName like '[a-f]%'
|
|
As you can see that I have used [a-f]%. That means I want the first character from a to f and after that any characters are fine as I used %.
4) [^] Any single character NOT within the specified rang [a-t] or set [abc]
Let's say if I want to find all the rows where FName first character Dost NOT start with [a to f]. We can use below query.
Select * From dbo.Customer
where FName like '[^a-f]%'
|
|
Noticed that it only returned us the rows which does not start with any character from a-f.
Let's say that if we are want to get all the rows where FName does not start with a,d,j. we can use below query.
Select * From dbo.Customer
where FName like '[^adj]%'
