MySQL LIKE Operator

In this tutorial, you will learn all about MySQL LIKE operator to query data from the MySQL Table based on the specified pattern. Using MySQL LIKE operator in MySQL, you can select all the data, who match the specified pattern.

Headings of Contents

1 MySQL LIKE Operator Introduction
2 MySQL LIKE Operator Syntax
3 Demo Table
4 MySQL Like LIKE Examples
5 Conclusion

MySQL LIKE Operator Introduction

The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. Sometimes we need only those records that follow a specific pattern, then we can use like operator to specify patterns in a column.

There are two wildcards often used in like operator.

% :- The percent sign represent zero, one or more character.
_ :- The underscore represent single character.

MySQL LIKE Operator Syntax

The following is the syntax of MySQL LIKE Operator.

SELECT column_list FROM table_name WHERE column LIKE pattern;

Let’s breakdown the above code:

SELECT command is used to select select the records from the table.
column_list represent the column which we want to select.
table_name represent the table name name.
WHERE clause is used to with LIKE operator.
column represent the column name where you want to apply LIKE operator.
pattern represent the pattern.

Demo Table

We have a demo table named students which will use throughout the program.

Table:- students


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |      10 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

MySQL Like LIKE Examples

We are going to use both the wildcards % and _ with the MySQL LIKE Operator.

a). Using MySQL LIKE Operator with % wildcard.

Example:

In this example, we will select all the students records whose first_name starts with ‘A’:

SELECT * FROM students WHERE first_name LIKE "A%";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
+-------+------------+-----------+--------+---------------------+---------+

Example:

In this example we will select all the students records whose last_name ends with ‘r‘:

SELECT * FROM students WHERE last_name LIKE "%r";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
+-------+------------+-----------+--------+---------------------+---------+

Example:

In this example, we will select all the students records whose last_name contains ‘ar‘ in any position.

SELECT * FROM students WHERE last_name LIKE "%ar%";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |      26 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
+-------+------------+-----------+--------+---------------------+---------+

b). Using MySQL LIKE Operator with an underscore ( _ ).

Example

In this example, we will select all the students records, whose first_name contains ‘a‘ on the second position.

SELECT * FROM students WHERE first_name LIKE "_a%";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

Example

Selecting all the students records whose last_name contains “m” on the third position from the last of the name.

SELECT * FROM students WHERE last_name LIKE "%m__";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |      23 |
+-------+------------+-----------+--------+---------------------+---------+

c). Using MySQL NOT LIKE Operator:

MySQL also allows us to use the NOT with LIKE operator to find records whose does not match the specified pattern.

Example

Suppose you want to select all the students whose first_name is not stats with “A“.

SELECT * FROM students WHERE first_name NOT LIKE "A%";

Output


+-------+------------+-----------+--------+---------------------+---------+
| st_id | first_name | last_name | course | created_at          | roll_no |
+-------+------------+-----------+--------+---------------------+---------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |      10 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |      19 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |      25 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |      12 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |      40 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |      17 |
+-------+------------+-----------+--------+---------------------+---------+

Note:- You have to remember one thing, pattern name is not case sensitive that means “A%” and “a%” return the same result.

Conclusion

So, in this article, you have seen all about MySQL LIKE Operator with the help of examples.LIKE operator in MySQL is one of the most useful and powerful operators especially when you want to search specific records from the table on the basis of a specified query.

If you like this article, please share and keep visiting for further MySQL tutorials.