Wednesday 25 May 2016

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

Popular Posts