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

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:

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:

This query shows the following table in the output:

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:

This query shows the following table in the output:

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:

This query shows the following table in the output:

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:

This query shows the following table in the output:

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

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:

This query shows the following table in the output:

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:

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:

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

The above query shows the following table in the output:

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

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:

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:

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

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:

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

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:

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

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:

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

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:

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

The above query shows the following table in the output:

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:

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

The above query shows the following table in the output:

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

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

The above query shows the following table in the output: