Here we will learn what is like operator in sql server, how to use like operator in sql server, use of like operator in sql server and how to use like operator with not keyword in sql server.
The LIKE operator in SQL is used to search for character string with specified pattern using wildcards in column. In SQL pattern means its specific string of characters with wild cards to search for matched expressions. Generally we will use this LIKE operator in WHERE clause and syntax for LIKE operator in SQL will be like as shown below
Following sql statement represent syntax of sql like operator
SELECT column1, column2 FROM tablename WHERE column1 LIKE 'SomePatternwithwildcard'
Note: You will Learn more about sql wildcards in next chapter.
We will check sql like operator with example for that first create “EmployeeDetails” table by using following script
create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))
insert into EmployeeDetails
values(1,'suresh','software engineer',25000,'chennai'),
(2,'rohini','AEO',15000,'chennai'),
(3,'madhavsai','business analyst',50000,'nagpur'),
(4,'mahendra','CA',75000,'guntur'),
(5,'sateesh','Doctor',65000,'guntur')
select * from EmployeeDetails
Once we run above SQL script our table “EmployeeDetails” will create and result will be like as shown below
Now run following examples to check LIKE operator in SQL
The following SQL query will return all employees with location starts with character 'c' followed by any string of characters because we mentioned pattern like 'c%'. Here '%' is wildcard character which we will use before or after characters to search for required matched string of characters.
SELECT * FROM EmployeeDetails WHERE Location LIKE 'c%'
Once we run above sql query our sql like operatpr example result will be like as shown below
Output
Following is the output of sql like operator example
The following SQL query will return all employees with location end with character 'r' because we mentioned pattern like '%r'. This means return all records with pattern matches like location ends with character 'r'.
SELECT * FROM EmployeeDetails WHERE Location LIKE '%r'
When we run above sql query we will get sql like operator result will be like as shown below
Output
Following is the output of sql like operator example
The following SQL query will return all employees with location containing word 'en' anywhere in the location column because we mentioned pattern like '%en%'. This means it will check for the respective word anywhere in column irrespective of characters in front or back.
SELECT * FROM EmployeeDetails WHERE Location LIKE '%en%'
When we run above sql query we will get sql like operator result like as shown below
Output
Following is the output of sql like opeator example
Now we will see how to use NOT keyword with LIKE operator and will check how it will return records. Generally if we use NOT keyword with LIKE operator it will return all the records that does not match the pattern.
The following SQL statement will return all employees with location not containing word 'en' anywhere in the location column because we used NOT keyword with LIKE operator and mentioned pattern like '%en%'.
SELECT * FROM EmployeeDetails WHERE Location NOT LIKE '%en%'
When we run above sql query we will get result of sql like operator with not keyword example will be like as shown below
Output
Following is the output of sql like operator with not keyword example
