SQL LIKE Operator

In sql, LIKE operator is used to search for character string with 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 sql server.

 

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 execute above sql script the table “EmployeeDetails” will create and result will be like as shown below.

 

Newly created employeedetails table in sql server

 Now run 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 above sql query our sql like operatpr 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 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 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 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 does 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

 

PREVIOUS

SQL ALL Operator
 

NEXT

SQL IN Operator