Monday, August 11, 2014

Like() function in SQL Server

Like:- This operator is used in a WHERE clause to search for a specific pattern in the values of the column.

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Nidhi','Sharma','nidhi@abcmail.com',270000)
 


For example, suppose we want to get the records of the employee whose firstname start with character 'N', then we can use the below query:-

select * from employee where EmpFirstName  like 'N%'

This query will returns the records for the employee whose empnumber is given below:

A003
A004

Again, suppose we want to get the records of the employees whose last name end with character 'a', then we can use the below query:-

Select * from employee where EmplastName  like '%a'

This query will returns the records for the employee whose empnumber is given below:-

A003
A004

Now, suppose we want to get the records of the employees whose last name contains character 'ha', then we can use the below query:-

select * from employee where EmplastName  like '%ha%'

This query will returns the records for the employees whose empnumber is given below:

A001
A003
A004

DMCA.com

No comments:

Post a Comment