SQL LIKE Operator

In SQL, LIKE operator is used to search for character string with the specified pattern using wildcards in column. In SQL, pattern means its a specific string of characters with wild cards to search for matching expressions.

 

Generally, we will use this LIKE operator in WHERE clause and syntax for LIKE operator in SQL will be like as shown below.

Syntax of SQL LIKE Operator

Following is the syntax of like operator in the SQL server.

 

SELECT column1, column2 FROM tablename WHERE column1 LIKE 'SomePatternwithwildcard'

Note: You will Learn more about SQL wildcards in the 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 execute the above SQL script the table “EmployeeDetails” will create and the result will be as shown below.

 

Newly created employeedetails table in sql server

 Now run the following examples to check LIKE operator in SQL server.

SQL LIKE Operator Example1

The following SQL like query will return all employees whose location starts with character 'c' and followed by any string of characters because here we mentioned a pattern like 'c%'. Here, the '%' is a 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 execute the above SQL query our SQL like operator example result will be like as shown below.

 

SQL Like Operator Example Result or Output

SQL Like Operator Example2

The following SQL query will return all employees whose location end with character 'r' because here, we mentioned a pattern like '%r', this means it will return all the records whose location ends with a character 'r'.

 

SELECT * FROM EmployeeDetails WHERE Location LIKE '%r'

When we execute the above SQL like query, we will get the result like as shown below.

 

SQL Like

SQL Like Operator Example3

The following SQL query will return all the employees whose location contains a word called 'en' anywhere in the location column because we mentioned a pattern like '%en%'. This means it will check for the respective word anywhere in the column irrespective of characters in front or back.

 

SELECT * FROM EmployeeDetails WHERE Location LIKE '%en%'

When we execute the above SQL like query, we will get the result like as shown below.

 

SQL Like Operator Example Result or Output

Now we will see how to use NOT keyword with a LIKE operator and will check how it will return records. Generally, if we use NOT keyword with LIKE operator it will return the records that do not contain a matching pattern.

SQL LIKE Operator with NOT Keyword Example

The following SQL statement will return all the employees whose location not containing a word called 'en', anywhere within the location column because we used a NOT keyword with LIKE operator and mentioned a pattern like '%en%'.

 

SELECT * FROM EmployeeDetails WHERE Location NOT LIKE '%en%'

When we execute above SQL like operator with not keyword example query, we will get the result like as shown below.

 

SQL Server Like Operator with Not Keyword Example