|
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 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)
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)
| 
