LIKE clause in SQL server

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 3^rd 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