What is LIKE Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 123

What is LIKE Logical Operator : 

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%'

How to use % with Like Operator in SQL Server - SQL Server / TSQL Tutorial














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'
How to use underscore( _ ) with Like Operator in SQL Server - SQL Server / TSQL Tutorial

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]%'

How to use Rang with Like operator in SQL Server for search - SQL Server / TSQL Tutorial

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]%'

How to use Not in Range with Like Operator in SQL Server - SQL Server / TSQL Tutorial


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]%'



Video Demo : How to use Logical Like Operator in SQL Server


No comments:

Post a Comment