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