SQL LIKE Operator

In SQL, the LIKE operator searches for character strings with the specified pattern using wildcards in columns. In SQL, pattern means a specific string of characters with wild cards to search for matching expressions.

Syntax of SQL LIKE Operator

Generally, we will use this LIKE operator in the WHERE clause. Following is the syntax of a 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 examples for that first create “EmployeeDetails” table using the 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 following is the result.

Newly created employeedetails table in sql server

Now run the following examples to check the LIKE operator in the 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 we mentioned a pattern like 'c%'. Here, the '%' is a wildcard character that we will use before or after characters to search for a 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 ends with character 'r' because we mentioned a pattern like '%r', which 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 below result.

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

SQL Like Operator Example Result or Output

Now we will see how to use the NOT keyword with a LIKE operator and check how it will return records. Generally, using the NOT keyword with the LIKE operator 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 does not contain 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 the above SQL like operator with not keyword example query, we will get the result below.

SQL Server Like Operator with Not Keyword Example