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 2nd 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 2nd 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 2nd 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 2nd 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.
Recommended Articles
We hope that this EDUCBA information on “LIKE in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
