Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

Sometime LIKE clause may not be useful when trying to search and select specific words. For example, if we used LIKE '%php%', we would get all the records which is wrong. To solve this issue, we can use find_in_set() function.


Dummy data


mysql> SELECT id, category FROM post;
+----+-----------------+
| id | category |
+----+-----------------+
| 1 | SYMFONY,PHPSPEC |
| 3 | GIT,PHP,LINUX |
| 9 | PHP |
| 10 | PHP,A |
| 11 | A,PHP |
| 12 | A,PHP,B |
| 73 | LINUX,PHP |
| 74 | LINUX,PHP |
| 77 | LINUX,PHING,PHP |
| 78 | LINUX,PHING,PHP |
+----+-----------------+
10 rows in set (0.00 sec)

LIKE clause


First record is not what we wanted so this is wrong way of doing it.


mysql> SELECT id, category FROM post WHERE category LIKE '%php%';
+----+-----------------+
| id | category |
+----+-----------------+
| 1 | SYMFONY,PHPSPEC |
| 3 | GIT,PHP,LINUX |
| 9 | PHP |
| 10 | PHP,A |
| 11 | A,PHP |
| 12 | A,PHP,B |
| 73 | LINUX,PHP |
| 74 | LINUX,PHP |
| 77 | LINUX,PHING,PHP |
| 78 | LINUX,PHING,PHP |
+----+-----------------+
10 rows in set (0.00 sec)

find_in_set() function


mysql> SELECT id, category FROM post WHERE find_in_set('PHP', category) <> 0;
+----+-----------------+
| id | category |
+----+-----------------+
| 3 | GIT,PHP,LINUX |
| 9 | PHP |
| 10 | PHP,A |
| 11 | A,PHP |
| 12 | A,PHP,B |
| 73 | LINUX,PHP |
| 74 | LINUX,PHP |
| 77 | LINUX,PHING,PHP |
| 78 | LINUX,PHING,PHP |
+----+-----------------+
9 rows in set (0.00 sec)