The LIKE operator is used to search for a specified pattern
in a column. It is used to list the all rows in a table whose column value
match a specified pattern. It is used when we want to search row to specific
pattern or we don’t know the entire value. There are two wildcards used in
conjunction with the LIKE operator:
Ø
The percent sign (%)
Ø
The underscore (_)
The percent sign represents zero, one, or multiple
characters. The underscore represents a single number or character. The symbols
can be used in combinations.
Wildcard
|
Description
|
%
|
A substitute for zero or
more characters
|
_
|
A substitute for a single
character
|
[charlist]
|
Sets and ranges of
characters to match
|
[^charlist]
or [!charlist] |
Matches only a character
NOT specified within the brackets
|
Let’s see the example
Note: I
am using AdventureWorksDW2008R2 Database and DimEmployee table
1.
Get all the
employee who have the word Rob anywhere in their First name
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like '%Rob%'
|
2.
Get all the
employee whose First Name start with the word Da
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like 'Da%'
|
3.
Get the all
employee whose name start from a, b, c or d
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like '[a-d]%'
--where FirstName like '[abcd]%'
--where FirstName like '[a,b,c,d]%'
--You can use any of them.
|
4. Get all the Employees whose First Name starts with the character a or b or c or d and
second character must be a
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like '[a-d][a]%'
--where FirstName like '[abcd][a]%'
--where FirstName like '[a,b,c,d][a]%'
|
5.
Get all the
Employees whose Name is not starting with letter a or b or
c or d
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like '[^a-d]%'
--where FirstName like '[^abcd]%'
--where FirstName like '[a,b,c,d]%'
|
6.
Get all the
Employees whose first Name must have 3rd character with letter a
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where FirstName like '__[a]%'
--here 2 underscore __
|
7.
Get all the
Employees whose hire on 2001-01
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where HireDate like '2002-01%'
|
8.
Get the all
employee whose Middle name is null
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where MiddleName like 'NULL%'
|
Above sql script is returning nothing. But we have Null value
in Middle name. You may have confused why not it returning anything.
The reason behind it.
The above
query didn’t return the employee whose is Middle name column value is NULL. As we
know that the NULL means unknown value, it can’t be compared with a known
value.
For getting the desirer
output we need to use below sql script.
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where MiddleName is null
|
9.
Get the all
employee whose middle name is not null.
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where MiddleName is not null
|
