Updated May 10, 2023

Introduction to LIKE in MySQL

In this article, we will learn how to use MySQL’s LIKE operator to fetch records based on specified patterns in the string. This LIKE operator is always used with WHERE clause in SELECT, UPDATE, and DELETE commands/statements.

Mainly throughout the course, we will use this operator with the SELECT command, which comes under Data Query Language (DQL). The SELECT command selects the table from which we need to fetch the records, WHERE clause is used as passing conditions in a query.

Syntax:

MySQL provides two kinds of special characters for constructing our conditions to apply to strings.

Percentage (%) matches strings of zero or more characters in the database.
Underscore (_) matches only a single character in the database.

Basic syntax:

SELECT column_name FROM table_name
WHERE column_name LIKE "A%";

(here we search for the field column_name in the table table_name where column_name’s records start with A)

How LIKE work in MySQL?

Let’s consider we have a student table containing the following attributes,

Student_id
First_name
Last_name
Marks

If we want to fetch records based on their names, then the LIKE operator will come into play.

Example 1:

SELECT stuent_id, First_name, Marks FROM student
WHERE first_name LIKE "A%";

(here, it will fetch all records of the student whose first name starts with ‘A’, like Aseem, Abhishek, etc..)

Example 2:

SELECT stuent_id, First_name, Marks FROM student
WHERE first_name LIKE "%a%";

(this will fetch records of a student with an ‘a’ character in their first_name, like Sohan, Sakti, etc..)

Example 3:

SELECT stuent_id, First_name, Marks FROM student
WHERE first_name LIKE "%n";

(this will fetch records of a student whose first_name ends with ‘n’ like Rohan, Raman, etc.. )

Example 4:

SELECT stuent_id, First_name, Marks FROM student
WHERE first_name LIKE "_a%";

(this will fetch records of a student whose first_name’s 2^nd character is ‘a’ like Aadit, Raghav, etc..)

Example 5:

SELECT stuent_id, First_name, Marks FROM student
WHERE first_name LIKE "%a_";

(this will fetch records of student whose first_name’s 2^nd character from last is ‘a’ like Sohan )

Examples to Implement LIKE in MySQL

Let us create one customer table

Cust_id	First_name	Last_name	Contact
1009	Ajinkya	Rahane	8746874464
1005	Hardik	Pandya	5435555426
1007	Jasprit	Bumrah	9875986763
1002	Virat	Kohli	7487687648
1010	Ravi	Sashtri	9759878404
1006	Krunal	Pandya	9874989859
1012	Bhubaneswar	Kumar	3547687379
1004	Virendra	Shewag	8765876876
1003	Sachin	Tendulkar	9878749867
1008	Anil	Kumble	9856876755
1001	Rohit	Sharma	2986746767
1011	Rahul	Dravid	5876874676

We will write some queries and see how it will fetch the record.

Query Example #1

SELECT * FROM customer
WHERE last_name LIKE "%a_";

(this will fetch records of students whose last_name’s 2^nd character from last is ‘a’ )

Output:

Cust_id	First_name	Last_name	Contact
1007	Jasprit	Bumrah	9875986763
1012	Bhubaneswar	Kumar	3547687379
1004	Virendra	Shewag	8765876876
1003	Sachin	Tendulkar	9878749867

Query Example #2

SELECT stuent_id, First_name, Marks FROM customer
WHERE first_name LIKE "_a%";

(this will fetch records of a student whose first_name’s 2^nd character is ‘a’ like Aadit, Raghav, etc.)

Output:

Cust_id	First_name	Last_name	Contact
1005	Hardik	Pandya	5435555426
1007	Jasprit	Bumrah	9875986763
1010	Ravi	Sashtri	9759878404
1003	Sachin	Tendulkar	9878749867
1011	Rahul	Dravid	5876874676

Query Example #3

SELECT stuent_id, First_name, Marks FROM customer
WHERE first_name LIKE "%t";

(this will fetch records of the student whose first_name ends with ‘t’ like Rohan, Raman, etc.. )

Output:

Cust_id	First_name	Last_name	Contact
1007	Jasprit	Bumrah	9875986763
1002	Virat	Kohli	7487687648
1001	Rohit	Sharma	2986746767

Query Example #4

SELECT stuent_id, First_name, Marks FROM customer
WHERE first_name LIKE "%a%";

(this will fetch records of a student with an ‘a’ character in their first_name, like Sohan, Sakti, etc..)

Output:

Cust_id	First_name	Last_name	Contact
1009	Ajinkya	Rahane	8746874464
1005	Hardik	Pandya	5435555426
1007	Jasprit	Bumrah	9875986763
1002	Virat	Kohli	7487687648
1010	Ravi	Sashtri	9759878404
1006	Krunal	Pandya	9874989859
1012	Bhubaneswar	Kumar	3547687379
1004	Virendra	Shewag	8765876876
1003	Sachin	Tendulkar	9878749867
1008	Anil	Kumble	9856876755
1011	Rahul	Dravid	5876874676

Query Example #5

SELECT stuent_id, First_name, Marks FROM customer
WHERE (first_name LIKE "A%") OR (last_name LIKE "K%");

(here, it will fetch all records of a student whose first name starts with ‘A’ or whose last name starts with ‘k’)

Output:

Cust_id	First_name	Last_name	Contact
1009	Ajinkya	Rahane	8746874464
1002	Virat	Kohli	7487687648
1012	Bhubaneswar	Kumar	3547687379
1008	Anil	Kumble	9856876755

Query Example #6

DELETE FROM customer
WHERE first_name IN (SELECT stuent_id, First_name, Marks FROM customer
WHERE first_name LIKE "%t");

Output:

Cust_id	First_name	Last_name	Contact
1009	Ajinkya	Rahane	8746874464
1005	Hardik	Pandya	5435555426
1010	Ravi	Sashtri	9759878404
1006	Krunal	Pandya	9874989859
1012	Bhubaneswar	Kumar	3547687379
1004	Virendra	Shewag	8765876876
1003	Sachin	Tendulkar	9878749867
1008	Anil	Kumble	9856876755
1011	Rahul	Dravid	5876874676

Conclusion

As we saw how this LIKE operator works with SELECT and DELETE, in the same manner, we can use this with the UPDATE command as well for filtering out our records. Two other operators, SUBSTR and INSTRIG, also work with string values for filtering out records. In real-time cases like, if we don’t know a person’s full name but remember some characters from his name, this LIKE operator can help us find that record.