LIKE

LIKE is used to search for a specified pattern in a field, usually with the % wildcard operator:

mysql> select * from Students where Town LIKE ‘%d’;
+----+-------+---------+-----+---------+------+-------+-------+
| id | First | Surname | Age | Town    | PIN  | Study | Score |
+----+-------+---------+-----+---------+------+-------+-------+
| 2  | James | Cross   | 20  | Watford | 7519 | Music | 88    |
+----+-------+---------+-----+---------+------+-------+-------+
1 row in set (0.00 sec)

 

 

The wildcard operator can be placed wherever required, for example %o%o%:

mysql> select * from Students where Town LIKE ‘%o%o%’;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 3  | Andy  | Jones   | 34  | Croydon | 8198 | Markets  | 64    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
| 6  | Phil  | Watson  | 36  | Hook    | 9437 | Cars     | 44    |
+----+-------+---------+-----+---------+------+----------+-------+
5 rows in set (0.00 sec)

 

 

Negate using NOT:

mysql> select * from Students where Town NOT LIKE ‘%o%o%’;
+----+-------+---------+-----+---------+------+-------+-------+
| id | First | Surname | Age | Town    | PIN  | Study | Score |
+----+-------+---------+-----+---------+------+-------+-------+
| 2  | James | Cross   | 20  | Watford | 7519 | Music | 88    |
| 7  | Terry | Waite   | 58  | Manc    | 2643 | Music | 59    |
| 8  | Alex  | Rose    | 49  | Cheam   | 0672 | C     | 70    |
+----+-------+---------+-----+---------+------+-------+-------+
3 rows in set (0.00 sec)

Leave a Reply