MySQL Pattern Matching

Tutorials

Mysql

Topic

How to Use Pattern Matching in select Statement?

Explanation

MySQL Pattern Matching :

Sometimes we may need to look for the table with a certain matching character. In MySQL we use LIKE or NOT LIKE operator for comparison. In MySQL the patterns are case-insensitive by default.

Let us consider an example of pattern matching query to display the student names starting with the letter M.


mysql> select * from student where name like 'm%';
+--------+---------+-------+-----------------+---------+
| studid | name    | marks | address         | phone   |
+--------+---------+-------+-----------------+---------+
|      3 | michael |    75 | edinburgh       | 2598234 |
|      8 | mille   |    98 | victoria street | 1236547 |
+--------+---------+-------+-----------------+---------+
2 rows in set (0.01 sec)

In the above example query, it will list all the names that starts with the letter M from the table student.

The following example query will list the names that ends with letter e.


mysql> select * from student where name like '%e';
+--------+-------+-------+------------------+---------+
| studid | name  | marks | address          | phone   |
+--------+-------+-------+------------------+---------+
|      1 | steve |   100 | 5th cross street | 2456987 |
|      5 | anne  |   100 | downing street   | 2634821 |
|      6 | steve |    75 | downing street   | 2874698 |
|      7 | anne  |    80 | edinburgh        | 2569843 |
|      8 | mille |    98 | victoria street  | 1236547 |
+--------+-------+-------+------------------+---------+
5 rows in set (0.00 sec)

The above and following examples shows how to use the mysql pattern matching and the types of pettern matchings. We can also list the names that contains a specific letter anywhere. The following example query will list the names that contains "a".


mysql> select * from student where name like '%a%';
+--------+---------+-------+-----------------+---------+
| studid | name    | marks | address         | phone   |
+--------+---------+-------+-----------------+---------+
|      2 | david   |    98 | welling street  |  547896 |
|      3 | michael |    75 | edinburgh       | 2598234 |
|      4 | jack    |    82 | victoria street | 2436821 |
|      5 | anne    |   100 | downing street  | 2634821 |
|      7 | anne    |    80 | edinburgh       | 2569843 |
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)

Suppose if we want to find the names that contain exactly five characters, we use a special character "_"(underscore). The following query will list all the five letter names from the table student.


mysql> select * from student where name like '_____';
+--------+-------+-------+------------------+---------+
| studid | name  | marks | address          | phone   |
+--------+-------+-------+------------------+---------+
|      1 | steve |   100 | 5th cross street | 2456987 |
|      2 | david |    98 | welling street   |  547896 |
|      6 | steve |    75 | downing street   | 2874698 |
|      8 | mille |    98 | victoria street  | 1236547 |
+--------+-------+-------+------------------+---------+
4 rows in set (0.00 sec)

Mysql Pattern Matching