How to Use LIKE in SQL

In this article, we will learn how to use LIKE to the column in the table of SQL database.

What is Like?

The LIKE is a SQL operator used to search for a particular pattern in each row of the field. This operator is always used with the WHERE clause in the SQL statement.

The syntax for using the Like operator in Structured Query Language:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE Pattern;

In Structured Query Language, the LIKE operator is used in conjunction with the following two wildcard characters:

Percent Sign (%)
Underscore (_)

Percent Sign (%)

This sign or wildcard character compares any string with zero, one, or multiple characters.

Syntax of using Percent Sign with LIKE operator:

1. The following syntax matches all the strings which begin with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%' ;

2. The following syntax matches all the strings which end with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%' ;

3. The following syntax matches all the strings which contain the character 'M' at any position:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE '%M%' ;

4. The following syntax matches all the strings which begin with the character 'M' and end with character 'T':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M%T' ;

Underscore (_)

This wildcard character in SQL compares any string with a single character.

Syntax of using Underscore Sign with LIKE operator:

1. The following syntax matches all the strings which contain only two characters:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M_' ;

2. The following syntax matches all the strings which contain the character 'm' at the second position:

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE '_m%' ;

3. The following syntax matches all the strings which contain at least 5 characters and begin with the character 'M':

SELECT Column_Name_1, Column_Name_2, ......, Column_Name_N FROM Table_Name WHERE Column_Name LIKE 'M_____%' ;

If you want to add the SQL LIKE operator to the column in the table, you have to follow the following steps in the given sequence:

Create a database in the system.
Create the table in the database and insert the data into the database.
View the inserted data
Use the LIKE operator to the column of the table.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

Suppose you want to create a College database. For this, you have to type the following command in Structured Query Language:

Step 2: Create a Table and Insert the data

Now, use the following SQL syntax for creating the table in your database:

CREATE TABLE table_name  
(
column_Name_1 data type (size of the column_1),  
column_Name_2 data type (size of the column_2),  
column_Name_3 data type (size of the column_3),  
...  
column_Name_N data type (size of the column_1)
);  

Suppose you want to create the Student table with five columns in the College database. For this, you have to write the following query in your application:

CREATE TABLE Student 
(
Roll_No Int,  
First_Name VARCHAR (20),  
City VARCHAR (20),  
Age Int,
Percentage Int,
Grade VARCHAR (10) 
) ;

Now, you have to insert the data in the table using the following syntax:

Use the following query to insert the record of multiple students in the Student table of the College database:

INSERT INTO Student VALUES (101, Akash, Delhi, 18, 89, A2), 
(102, Bhavesh, Kanpur, 19, 93, A1),
(103, Yash, Delhi, 20, 89, A2),  
(104, Bhavna, Delhi, 19, 78, B1),
(105, Yatin, Lucknow, 20, 75, B1),
(106, Ishika, Ghaziabad, 19, 51, C1),
(107, Vivek, Goa, 20, 62, B2);

Step 3: View the Inserted Data

After table creation and data insertion, you can view the inserted record of the Student table by typing the following query in your SQL application:

Roll_No	First_Name	City	Age	Percentage	Grade
101	Akash	Delhi	18	89	A2
102	Bhavesh	Kanpur	19	93	A1
103	Yash	Delhi	20	89	A2
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1
106	Ishika	Ghaziabad	19	91	C1
107	Vivek	Goa	20	80	B2

Step 4: Use the Like operator to the column in the table

The following query shows the record of those students from the Student table whose First_Name starts with 'B' letter:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE 'B%' ;

Output of above query:

Roll_No	First_Name	Percentage	Grade
102	Bhavesh	93	A1
104	Bhavna	78	B1

As shown in the above output, the table only contains the record of Bhavesh and Bhavna because their names begin with B letters.

The following query shows the record of those students from the Student table whose First_Name ends with the 'h' letter:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE '%h' ;

Output of above query:

Roll_No	First_Name	Percentage	Grade
101	Akash	89	A2
102	Bhavesh	93	A1
103	Yash	89	A2

As shown in the above output, the table only contains the record of Akash, Bhavesh, and Yash students because their names end with the letter h.

The following query shows the record of those students from the given Student table whose First_Name contains the character 'a' in any position:

SELECT Roll_No, First_Name, Percentage, Grade FROM Student WHERE First_Name LIKE '%a%' ;

Output of above query:

Roll_No	First_Name	City	Age	Percentage	Grade
101	Akash	Delhi	18	89	A2
102	Bhavesh	Kanpur	19	93	A1
103	Yash	Delhi	20	89	A2
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1
106	Ishika	Ghaziabad	19	91	C1

As shown in the SQL output, the table contains the record of all students except Vivek student because Vivek name does not contain the letter 'a' in any position.

The following query shows the record of those students from the Student table whose city name begins with the 'D' letter and ends with the 'I' letter:

SELECT Roll_No, First_Name, City, Percentage FROM Student WHERE City LIKE 'D%i' ;

Output of above query:

Roll_No	First_Name	City	Percentage
101	Akash	Delhi	89
103	Yash	Delhi	89
104	Bhavna	Delhi	78

As shown in the above SQL output, the table only contains the record of those students whose City is Delhi.

The following query shows the record of those students from the Student table whose Percentage start with the '7' digit:

SELECT Roll_No, First_Name, City, Percentage, Grade FROM Student WHERE Percentage LIKE '7_' ;

Output of above query:

Roll_No	First_Name	City	Age	Percentage	Grade
104	Bhavna	Delhi	19	78	B1
105	Yatin	Lucknow	20	75	B1

As shown in the above SQL output, the table only contains the record of those students whose marks is 78 and 75.

The following query shows the record of those students from the Student table whose First_Name contains 'a' at third position:

SELECT Roll_No, First_Name, City, Percentage, Grade FROM Student WHERE First_Name LIKE '__a%' ;

Output of above query:

Roll_No	First_Name	City	Percentage	Grade
101	Akash	Delhi	89	A2
102	Bhavesh	Kanpur	93	A1
104	Bhavna	Delhi	78	B1

As shown in the above output, the table only contains the record of those students whose First_Name contains the character 'a' at the third position.

Next TopicCursor in SQL

← prev next →