SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

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_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad24

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_IdStudent_NameCityAge
5Sakshi PatilAurangabad22
7Pranoti ShendeAurangabad23
12Anushka SenAurangabad23
Pattern Matching in SQL

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_IdStudent_NameCityAge
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
7Pranoti ShendeAurangabad23
13Bhushan PachpandeBangalore25
18Tushar MahaleHyderabad24
Pattern Matching in SQL

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_IdStudent_NameCityAge
4Prateek ZimbrePune24
7Pranoti ShendeAurangabad23
Pattern Matching in SQL

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_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad24
Pattern Matching in SQL

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_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad24

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_IdStudent_NameCityAge
12Anushka SenChennai23
Pattern Matching in SQL

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_IdStudent_NameCityAge
2Utkarsh RokadeDelhi22
3Sourabh ChougaleDelhi23
4Prateek ZimbreDelhi24
7Pranoti ShendeDelhi23
13Bhushan PachpandeDelhi25
18Tushar MahaleDelhi24
Pattern Matching in SQL

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_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeDelhi22
3Sourabh ChougaleDelhi23
4Prateek ZimbreDelhi24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeDelhi23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenChennai23
13Bhushan PachpandeDelhi25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleDelhi24

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;
Pattern Matching in SQL