SQL Like

The LIKE is a logical operator in the Structured Query Language. This SQL operator is used in the WHERE clause with the following three statements:

1. SELECT Statement
2. UPDATE Statement
3. DELETE Statement

It filters the records from the columns based on the pattern specified in the SQL query.

Following are two wildcard characters that are used either in conjunction or independently with the SQL LIKE operator:

1. % (percent sign): This wildcard character matches zero, one, or more than one character.
2. _ (underscore sign): This wildcard character matches only one or a single character.

Syntax of the LIKE operator in SQL

SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE column_name LIKE pattern;   

In this syntax, the pattern is a sequence of characters that have to be searched in that column which is just specified after the WHERE clause.

Examples of LIKE Operator in SQL

In this article, we have taken the following different SQL examples which help you how to use the LIKE operator:

Example 1: Let’s take the following Employee table which helps you to analyze the LIKE operator with % sign:

Emp_ID	Name	Emp_Salary	Emp_Dept
1001	Vivek	9000	Finance
1002	Saket	4000	HR
1003	Raman	3000	Coding
1004	Suraj	6000	Coding
1005	Seenu	5000	HR
1006	Shubham	10000	Marketing
1007	Anaya	4000	Coding
1008	Parul	8000	Finance

i) Suppose, you want to filter the records of those employees whose names start with “S”. For this operation, you have to type the following query:

SELECT * FROM Employee WHERE Name LIKE 'S%' ;  

This query shows the following table in the output:

Emp_ID	Name	Emp_Salary	Emp_Dept
1002	Saket	4000	HR
1004	Suraj	6000	Coding
1005	Seenu	5000	HR
1006	Shubham	10000	Marketing

ii) Suppose, you want to filter the records of those employees whose department name ends with “g”. For this operation, you have to type the following query:

SELECT * FROM Employee WHERE Emp_Dept LIKE '%g' ;  

This query shows the following table in the output:

Emp_ID	Name	Emp_Salary	Emp_Dept
1003	Raman	3000	Coding
1004	Suraj	6000	Coding
1006	Shubham	10000	Marketing
1007	Anaya	4000	Coding

iii) Suppose, you want to show the name and salary of those employees whose department name starts with “C” and ends with “g”. For this operation, you have to type the following query:

SELECT Name, Emp_Salary FROM Employee WHERE Emp_Dept LIKE 'C%g' ;  

This query shows the following table in the output:

Name	Emp_Salary
Raman	3000
Suraj	6000
Anaya	4000

iv) Suppose, you want to show all records of those employees from the above employee table whose Name contains the letter “a” in any position. For this operation, you have to type the following query:

SELECT * FROM Employee WHERE Emp_Dept LIKE '%a%' ;  

This query shows the following table in the output:

Emp_ID	Name	Emp_Salary	Emp_Dept
1002	Saket	4000	HR
1003	Raman	3000	Coding
1004	Suraj	6000	Coding
1006	Shubham	10000	Marketing
1007	Anaya	4000	Coding
1008	Parul	8000	Finance

Example 2: Let’s take the following Student table which helps you to analyze the LIKE operator with _ (underscore) sign:

Roll_No	Name	Marks	Age
1	Raman	95	20
2	Kapil	60	19
3	Arun	85	17
4	Ram	92	18
5	Suman	55	20
6	Sanjay	88	18
7	Sheetal	65	19
8	Rakesh	64	20

i) Suppose, you want to show all records of those students whose Name contains “a” at the second position. For this operation, you have to type the following query with underscore sign:

SELECT * FROM Student WHERE Name LIKE '_a%' ;  

This query shows the following table in the output:

Roll_No	Name	Marks	Age
1	Raman	95	20
2	Kapil	60	19
4	Ram	92	18
6	Sanjay	88	18
8	Rakesh	64	20

ii) Suppose, you want to access records of those students whose names contain at least 3 characters and starts with the letter “S”. For this operation, you have to type the following query:

SELECT * FROM Student WHERE Name LIKE 'S___%' ;  

In this query, you have to use the underscore sign three times after the S character. The above query shows the following table in the output:

Roll_No	Name	Marks	Age
5	Suman	55	20
6	Sanjay	88	18
7	Sheetal	65	19

iii) Suppose, you want to access Roll_No, Name, and Marks of those students whose Marks is 2 digits long and ends with ‘5’:

SELECT * FROM Student WHERE Name LIKE '_5' ;  

The above query shows the following table in the output:

Roll_No	Name	Marks	Age
1	Raman	95	20
3	Arun	85	17
5	Suman	55	20
7	Sheetal	65	19

Like with UPDATE Statement

In SQL, we can also use the LIKE operator in the WHERE clause with the UPDATE statement. The LIKE operator updates those records in the table which satisfy the pattern specified in the query.

Syntax of LIKE with UPDATE Statement

UPDATE table_name SET column_Name1 = value1, column_Name2 = value2, ...., column_NameN = valueN WHERE ColumnName LIKE Pattern;  

Examples of LIKE with UPDATE Statement

Here, we have taken the following two different SQL examples which help you how to use the LIKE operator with UPDATE statement for updating the existing records in the tables:

Example 1: Let’s take the following Student table which shows you how to update records using LIKE operator with % (percent) sign in the UPDATE statement:

Roll_No	Name	Marks	City
1	Raman	95	Delhi
2	Kapil	60	Gurugram
3	Arun	85	Ghaziabad
4	Ram	92	Delhi
5	Suman	55	Ghaziabad
6	Sanjay	88	Gurugram
7	Sheetal	65	Gurugram
8	Rakesh	64	Delhi

i) Suppose, you want to update the city of those students whose Name starts with “S”. For this operation, you have to type the following query with underscore sign:

UPDATE Student SET City = 'Jaipur' WHERE Name LIKE 'S%';  

The above query sets the city as Jaipur of those students whose name starts with the letter ‘S’ and if you want to see the changes in the table then you have to type the following query:

Select * From Student Where Name LIKE 'S%' ;  

Roll_No	Name	Marks	City
5	Suman	55	Jaipur
6	Sanjay	88	Jaipur
7	Sheetal	65	Jaipur

ii) Suppose, you want to update the Marks of those students whose City name ends with “i”. For this operation, you have to type the following query with underscore sign:

UPDATE Student SET Marks = 70 WHERE City LIKE '%i';  

The above query sets the marks as 70 of those students whose name of the city ends with the letter ‘i’ and if you want to see the changes in the table then you have to type the following query:

Select * From Student Where City LIKE '%i';  

Roll_No	Name	Marks	City
1	Raman	70	Delhi
4	Ram	70	Delhi
8	Rakesh	70	Delhi

iii) Suppose, you want to update the Marks of those students whose Name of the city starts with “G” and ends with “d”. For this operation, you have to type the following query with underscore sign:

UPDATE Student SET Marks = 90 WHERE City LIKE 'G%d' ;  

The above query sets the marks as 90 of those students whose name of the city starts with the letter ‘G’ and ends with the letter ‘d’ and if you want to see the changes in the table then you have to type the following query:

Select Roll_No, Marks, City From Student Where City LIKE 'G%d' ;  

Roll_No	Marks	City
3	90	Ghaziabad
5	90	Ghaziabad

iv) Suppose, you want to update the City of those students of the above student table whose Name contains the letter “a” in any position. For this operation, you have to type the following query:

UPDATE Student SET City = 'Goa' WHERE Name LIKE '%a%' ;  

The above query sets the City as Goa of those students whose name contains the letter “a” in any position in the table and if you want to see the changes in the table then you have to type the following query:

UPDATE Student SET City = 'Goa' WHERE Name LIKE '%a%' ;  

Roll_No	Name	Marks	City
1	Raman	95	Goa
2	Kapil	60	Goa
4	Ram	92	Goa
5	Suman	55	Goa
6	Sanjay	88	Goa
7	Sheetal	65	Goa
8	Rakesh	64	Goa

Example 2: Let’s take the following Employee table which shows you how to update records using LIKE operator with _ (underscore) sign in the UPDATE statement:

Emp_ID	Name	Emp_Salary	Emp_Dept
1001	Vivek	9000	Finance
1002	Saket	4000	HR
1003	Raman	3000	Coding
1004	Suraj	6000	Coding
1005	Seenu	5000	HR
1006	Shubham	10000	Marketing
1007	Anaya	4000	Coding
1008	Parul	8000	Finance

Table: Employee

i) Suppose, you want to update the salary of those employees whose Name contains “a” at the second position. For this operation, you have to type the following query with underscore sign:

UPDATE Employee SET Salary = 9000 WHERE Name LIKE '_a%' ;  

The above query sets the Salary as 9000 of those employees whose name contains the letter “a” at the second position in the table and if you want to see the changes in the table then you have to type the following query:

Select * From Employee WHERE Name LIKE  '_a%' ;  

Emp_ID	Name	Emp_Salary	Emp_Dept
1002	Saket	9000	HR
1003	Raman	9000	Coding
1008	Parul	9000	Finance

ii) Suppose, you want to update the department of those employees whose name contains at least 3 characters and starts with the letter “S”. For this operation, you have to type the following query:

UPDATE Employee SET Emp_Dept = 'Coding' WHERE Name LIKE 'S___%' ;  

In this query, you have to use the underscore sign three times after the S character.

The above query sets the Emp_Dept as Coding of those employees whose name contains at least 3 characters and starts with the letter “S” in the table and if you want to see the changes in the table then you have to type the following query:

SELECT * FROM Employee WHERE Name LIKE 'S___%' ;  

Emp_ID	Name	Emp_Salary	Emp_Dept
1002	Saket	4000	Coding
1004	Suraj	6000	Coding
1005	Seenu	5000	Coding
1006	Shubham	10000	Coding

iii) Suppose, you want to update the Salary of those employees whose Emp_Dept is 2 Characters long and ends with the character ‘R’. For this operation, you have to type the following query:

>UPDATE Employee SET Salary = 20000 WHERE Emp_Dept LIKE '_R' ;  

The above query sets the Salary as 20000 of those employees whose Emp_Dept is 2 Character long and ends with character ‘R’ in the table and if you want to see the changes in the table then you have to type the following query:

SELECT * FROM Employee WHERE Name LIKE '_R' ;  

Emp_ID	Name	Emp_Salary	Emp_Dept
1002	Saket	20000	HR
1005	Seenu	20000	HR

Like with DELETE Statement

In SQL, we can also use the LIKE operator in the WHERE clause with the DELETE statement. The LIKE operator deletes or removes those records from the table which matches with the pattern specified in the SQL query.

Syntax of LIKE with DELETE Statement

DELETE FROM table_name WHERE ColumnName LIKE Pattern ;  

Example of LIKE with DELETE Statement

Here, we have taken the following example which helps you how to use the LIKE operator with DELETE statement for deleting the existing records from the database table:

Let’s take the following Student table which shows you how to delete records using the LIKE operator with % (percent) and _(Underscore) sign:

Roll_No	Name	Marks	City
1	Raman	95	Delhi
2	Kapil	60	Gurugram
3	Arun	85	Ghaziabad
4	Ram	92	Delhi
5	Suman	55	Ghaziabad
6	Sanjay	88	Gurugram
7	Sheetal	65	Gurugram
8	Rakesh	64	Delhi

i) Suppose, you want to delete the record of those students whose Name starts with “S”. For this operation, you have to type the following query with underscore sign:

DELETE FROM Student WHERE Name LIKE 'S%';  

This query removes those records of students from the above student table whose name starts with S:

SELECT * FROM Student;  

Roll_No	Name	Marks	City
5	Suman	55	Ghaziabad
6	Sanjay	88	Gurugram
7	Sheetal	65	Gurugram

ii) Suppose, you want to delete the record of those students whose City name ends with “i”. For this operation, you have to type the following query with underscore sign:

DELETE FROM Student WHERE City LIKE '%i';  

This query removes those records of students from the above student table whose City name ends with i:

SELECT * FROM Student;  

Roll_No	Name	Marks	City
1	Raman	95	Delhi
4	Ram	92	Delhi
8	Rakesh	64	Delhi

iii) Suppose, you want to delete the record of those students whose Name of the city starts with “G” and ends with “d”. For this operation, you have to type the following query with underscore sign:

DELETE FROM Student WHERE City LIKE 'G%d';  

This query removes those records of students from the above student table whose Name of the city starts with “G” and ends with “d”.

SELECT * FROM Student;  

Roll_No	Name	Marks	City
1	Raman	95	Delhi
2	Kapil	60	Gurugram
4	Ram	92	Delhi
6	Sanjay	88	Gurugram
7	Sheetal	65	Gurugram
8	Rakesh	64	Delhi

iv) Suppose, you want to delete those records of the student table whose Name contains the letter “a” in any position. For this operation, you have to type the following query:

DELETE FROM Student WHERE City LIKE 'G%d';  

This query removes those records of students from the above student table whose Name contains the letter “a” in any position.

SELECT * FROM Student;  

This query does not show any table in the result, because all the rows were deleted from the table.

v) Suppose, you want to delete those records from the student table whose Name contains letter “a” at the second position. For this operation, you have to type the following query with underscore sign:

DELETE FROM Student WHERE Name LIKE '_a%';  

This query removes those records of students from the above student table whose Name contains the alphabet “a” at the second position.

SELECT * FROM Student;  

The above query shows the following table in the output:

Roll_No	Name	Marks	City
3	Arun	85	Ghaziabad
5	Suman	55	Ghaziabad
7	Sheetal	65	Gurugram

vi) Suppose, you want to delete those records from the student table whose name contains at least 3 characters and starts with the letter “S”. For this operation, you have to type the following query:

DELETE FROM Student WHERE Name LIKE 'S___%';  

This query removes those records of students from the above student table whose Name contains at least 3 characters and starts with the letter “S”.

SELECT * FROM Student;  

The above query shows the following table in the output:

Roll_No	Name	Marks	City
5	Suman	55	Ghaziabad
6	Sanjay	88	Gurugram
7	Sheetal	65	Gurugram

vii) Suppose, you want to delete those records from the student table whose Marks is 2 digits long and ends with ‘5’:

DELETE FROM Student WHERE Marks LIKE '_5' ;  

This query removes those records of students from the above student table whose ‘Marks’ is 2 digits long and ends with ‘5’.

SELECT * FROM Student;  

The above query shows the following table in the output:

Roll_No	Name	Marks	City
2	Kapil	60	Gurugram
4	Ram	92	Delhi
6	Sanjay	88	Gurugram
8	Rakesh	64	Delhi