SQL SELECT LIKE Operator

The SQL SELECT LIKE Operator tutorial helps us understand how to use the LIKE operator in the SELECT query with examples.

The SQL SELECT LIKE Operator retrieves the records from the table, which finds the data using wildcard options. The SQL LIKE operator is used with the WHERE clause in the query.

Following are the two wildcard characters are used to find the pattern matching string from the table.

1 Percent Wildcard Character (%)

2 Underscore Wildcard Character (_)

The syntax of the LIKE Operator with the SELECT statement is as follows:

SELECT Column_1, Column_2, Column_3. Column_4, Column_5 FROM Table_Name WHERE Column_Name LIKE expression;

1 Percent Wildcard Character (%): The Percent Wildcard Character symbol is used to search the zero, one, or more than one string character.

The syntax of the SQL SELECT LIKE operator using Percent wildcard Character is as follows:

SELECT Column_1, Column_2, Column_3. Column_4, Column_5 FROM Table_Name WHERE Column_Name LIKE %expression%;

2 Underscore Wildcard Character (%): The Underscore Wildcard Character symbol searches for the zero or one string character.

The syntax of the SQL SELECT LIKE operator using Underscore wildcard Character is as follows:

SELECT Column_1, Column_2, Column_3. Column_4, Column_5 FROM Table_Name WHERE Column_Name LIKE ___%;

We can use the AND operator and the OR operator for multiple combinations in the query.

Let’s understand the SQL SELECT LIKE operator with the help of an examples

Consider the already existing table with the certain records

Table Number 1: 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	Sonakashi Sen	Bangalore	25
16	Mayuri Wagh	Hyderabad	22
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Hyderabad	23

Table Number 2: Course

Course_Id	Course_Name	Student_Id	Duration
11	Cloud Computing	2	3
12	SQL Database	1	1
13	Advance Java	4	3
14	Data Structures	7	6
15	AWS	8	2
16	Angular Js	10	3
17	Oracle Integration Cloud	11	6
18	Python	6	1
19	ReactJs	1	3
20	Computing	6	4

Example 1: Execute a query to display the student information from the student table where the student name starts with the 'S' letter.

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

The above SELECT LIKE operator query example retrieved all the student information from the student table where the student name starts with the letter 'A'.

The output of the above query is as follows:

Student_Id	Student_Name	City	Age
3	Sourabh Chougale	Nashik	23
5	Sakshi Patil	Aurangabad	22
6	Shruti Sharma	Mumbai	21
11	Samaira Sharma	Mumbai	22
15	Sonakashi Sen	Bangalore	25

Example 2: Execute a query to display the student information from the student table where the student name ends with the 'E’ letter.

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

The above SELECT LIKE operator query example retrieved all the student information from the student table where the student name ends with the letter 'E'.

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	23

Example 3: Execute a query to display the student information from the student table where the student name contains the letter 'T' at any position.

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

The above SELECT LIKE operator query example retrieved all the student information from the student table where the student name contains the letter 'T' anywhere.

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
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
17	Ritika Patil	Hyderabad	21
18	Tushar Mahale	Hyderabad	23

Example 4: Execute a query to display the course information from the course table where the course name starts with 'A' and ends with  'S'.

SELECT * FROM Course WHERE Course_Name LIKE 'A%S';

The above SELECT LIKE operator query example retrieved all the student course information from the course table where the course name starts with the letter 'A' and ends with the letter 'S'.

The output of the above query is as follows:

Course_Id	Course_Name	Student_Id	Duration
15	AWS	8	2
16	Angular Js	10	3

Example 5: Write a query to display the student information from the student table whose student city contains U at the second position.

SELECT * FROM Student WHERE City LIKE '_U%';

The above SELECT LIKE operator query example retrieved all the student course information from the course table where the city name contains the letter 'U' at the second index.

The output of the above query is as follows:

Student_Id	Student_Name	City
1	Pratik Srivastav	Pune
2	Utkarsh Rokade	Mumbai
4	Prateek Zimbre	Pune
5	Sakshi Patil	Aurangabad
6	Shruti Sharma	Mumbai
7	Pranoti Shende	Aurangabad
10	Nikhil Patil	Pune
11	Samaira Sharma	Mumbai
12	Anushka Sen	Aurangabad

Example 6: Write a query to display the student information from the student table whose student city name is 6 letters.

SELECT * FROM Student WHERE City LIKE '______';

The above SELECT LIKE operator query example retrieved all the student course information from the student table. The city name contains only 6 letters, starts with any letter, and ends with any letter.

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
6	Shruti Sharma	Mumbai	21
8	Harshada Dhanwat	Nashik	24
9	Tejas Bairagi	Nashik	21
11	Samaira Sharma	Mumbai	22