Searching a specific keyword in a string with find_in_set()

19/06/2015 - MYSQL

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)