Pattern Matching in SQL

The LIKE in the Structured Query Language is a Logical Operator. The SQL LIKE is used within the WHERE clause in the SQL. This Like Operator is used with the mentioned statements:

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

The Like Operator in the Structured Query Language filters the records from the fields based upon the pattern used in the condition in the SQL query.

There are two Wildcard characters used with the SQL like Operator are as follows:

1. Percent Sign (%): This character is used to match either zero, one, or more than one character in the column.
2. Underscore (_): This character is used to match one or a single character.

The syntax for using the LIKE Operator is as follows:

SELECT * FROM TABLENAME WHERE COLUMN NAME LIKE PATTERN;

The pattern in the syntax is nothing but the pattern to be searched in the column.

Let's Look at Examples of LIKE Operators.

1. LIKE OPERATOR WITH SELECT STATEMENT

Consider the already existing table with the following data

Table Number: Student

Student_Id	Student_Name	City	Age
1	Pratik Srivastav	Pune	23
2	Utkarsh Rokade	Mumbai	22
3	Sourabh Chougale	Nashik	23
4	Prateek Zimbre	Pune	24
5	Sakshi Patil	Aurangabad	22
6	Shruti Sharma	Mumbai	21
7	Pranoti Shende	Aurangabad	23
8	Harshada Dhanwat	Nashik	24
9	Tejas Bairagi	Nashik	21
10	Nikhil Patil	Pune	24
11	Samaira Sharma	Mumbai	22
12	Anushka Sen	Aurangabad	23
13	Bhushan Pachpande	Bangalore	25
14	John Chaudhary	Bangalore	24
15	Sonakshi Sen	Bangalore	25
16	Mayuri Wagh	Hyderabad	22
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Hyderabad	24

First look at the Like Operator with the % sign:

Example 1:  For example, you want to display student information but want only those students whose city name starts with 'A'. For such a query, we will need to write the following query:

SELECT * FROM Student WHERE City LIKE ‘A%’;

We have displayed the student's information whose city name starts with the letter 'A' in the above query. 

The output of the above query is as follows:

Student_Id	Student_Name	City	Age
5	Sakshi Patil	Aurangabad	22
7	Pranoti Shende	Aurangabad	23
12	Anushka Sen	Aurangabad	23

Example 2:  Suppose you want to display a student's information but only that student whose name ends with 'e'. For such a query, we will need to write the following query:

SELECT * FROM Student WHERE Student_Name LIKE ‘%E’;

We have displayed the student's information whose name ends with the letter 'E' in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	City	Age
2	Utkarsh Rokade	Mumbai	22
3	Sourabh Chougale	Nashik	23
4	Prateek Zimbre	Pune	24
7	Pranoti Shende	Aurangabad	23
13	Bhushan Pachpande	Bangalore	25
18	Tushar Mahale	Hyderabad	24

Example 3:  For instance, you want to display the student's information but only those students whose name starts with 'P' and ends with 'E'. For such a query, we will need to write the following query:

SELECT * FROM Student WHERE Student_Name LIKE ‘P%E’;

We have displayed the student's information whose name starts with the letter 'P' and ends with the letter 'E' in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	City	Age
4	Prateek Zimbre	Pune	24
7	Pranoti Shende	Aurangabad	23

Example 4:  Suppose you want to display the student’s information but only those employees whose name contains 'a' at any index. For such a query, we will need to write the following query:

SELECT * FROM Student WHERE Student_Name LIKE ‘%A%’;

We have displayed the student's information whose name contains the letters 'A' at any position in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	City	Age
1	Pratik Srivastav	Pune	23
2	Utkarsh Rokade	Mumbai	22
3	Sourabh Chougale	Nashik	23
4	Prateek Zimbre	Pune	24
5	Sakshi Patil	Aurangabad	22
6	Shruti Sharma	Mumbai	21
7	Pranoti Shende	Aurangabad	23
8	Harshada Dhanwat	Nashik	24
9	Tejas Bairagi	Nashik	21
10	Nikhil Patil	Pune	24
11	Samaira Sharma	Mumbai	22
12	Anushka Sen	Aurangabad	23
13	Bhushan Pachpande	Bangalore	25
14	John Chaudhary	Bangalore	24
15	Sonakshi Sen	Bangalore	25
16	Mayuri Wagh	Hyderabad	22
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Hyderabad	24

2. LIKE OPERATOR WITH UPDATE STATEMENT: -

In the Structured Query Language, we can use Like Operator with an UPDATE statement in the Where Clause. The Update Statement will update only those records or data included in the query's pattern.

The syntax of the LIKE operator is as follows:

UPDATE TABLENAME SET COLUMNNAME = VALUES WHERE COLUMN NAME LIKE PATTERN(%,_);

Example of Like with Update Statement:

Here, we have taken or explained a few examples to help you understand the Like operation with the Update statement.

Consider the already existing table with the following data

Table Number: Student

Student_Id	Student_Name	City	Age
1	Pratik Srivastav	Pune	23
2	Utkarsh Rokade	Mumbai	22
3	Sourabh Chougale	Nashik	23
4	Prateek Zimbre	Pune	24
5	Sakshi Patil	Aurangabad	22
6	Shruti Sharma	Mumbai	21
7	Pranoti Shende	Aurangabad	23
8	Harshada Dhanwat	Nashik	24
9	Tejas Bairagi	Nashik	21
10	Nikhil Patil	Pune	24
11	Samaira Sharma	Mumbai	22
12	Anushka Sen	Aurangabad	23
13	Bhushan Pachpande	Bangalore	25
14	John Chaudhary	Bangalore	24
15	Sonakshi Sen	Bangalore	25
16	Mayuri Wagh	Hyderabad	22
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Hyderabad	24

Example 1: Suppose you want to update the student city whose student name starts with 'A'. For such a query, we will need to write the following query:

UPDATE Student SET City = 'Chennai' WHERE Student_Name LIKE 'A%';

We have set the student city name as 'Chennai', where the student’s name starts with the letter 'A' in the above query.

To check whether the query is successfully executed or not, execute Select Statement and see changes:

The output of the above query is as follows:

SELECT * FROM Student WHERE Student_Name LIKE 'A%';

Student_Id	Student_Name	City	Age
12	Anushka Sen	Chennai	23

Example 2: Suppose you want to update the student city name whose student’s name ends with ‘E’. For such a query, we will need to write the following query:

UPDATE Student SET City = 'Delhi' WHERE Student_Name LIKE '%E';

We have set the student city name as 'Delhi', where the student name ends with the letter 'E' in the above query.

To check whether the query is successfully executed or not, execute Select Statement and see changes:

The output of the above query is as follows:

SELECT * FROM Student WHERE Student_Name LIKE '%E';

Student_Id	Student_Name	City	Age
2	Utkarsh Rokade	Delhi	22
3	Sourabh Chougale	Delhi	23
4	Prateek Zimbre	Delhi	24
7	Pranoti Shende	Delhi	23
13	Bhushan Pachpande	Delhi	25
18	Tushar Mahale	Delhi	24

3. LIKE OPERATOR WITH DELETE STATEMENT: -

In the Structured Query Language, we can use Like Operator with a DELETE statement in the Where Clause. The Delete Statement will remove only those records or data included in the query's pattern.

The syntax of a LIKE operator is as follows:

DELETE FROM TABLENAME WHERE COLUMN NAME LIKE PATTERN(%,_);

Example of Like with Delete Statement:

Here, we have taken or explained a few examples to help you understand the Like operation with the Delete statement.

Consider the already existing table with the following data

Table Number: Student

Student_Id	Student_Name	City	Age
1	Pratik Srivastav	Pune	23
2	Utkarsh Rokade	Delhi	22
3	Sourabh Chougale	Delhi	23
4	Prateek Zimbre	Delhi	24
5	Sakshi Patil	Aurangabad	22
6	Shruti Sharma	Mumbai	21
7	Pranoti Shende	Delhi	23
8	Harshada Dhanwat	Nashik	24
9	Tejas Bairagi	Nashik	21
10	Nikhil Patil	Pune	24
11	Samaira Sharma	Mumbai	22
12	Anushka Sen	Chennai	23
13	Bhushan Pachpande	Delhi	25
14	John Chaudhary	Bangalore	24
15	Sonakshi Sen	Bangalore	25
16	Mayuri Wagh	Hyderabad	22
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Delhi	24

Example: Suppose you want to remove the student information whose student name contains the letter 'A' at any index. For such a query, we will need to write the following query:

DELETE FROM Student WHERE Student_Name LIKE ‘%A%’;

We have displayed the student's information whose name contains the letters 'A' at any position in the above query.

To check whether the query is successfully executed or not, just execute Select Statement and see changes:

The output of the above query is as follows:

SELECT * FROM Student;