January 10 2013

Mysql find_in_set() Function

Tagged Under : ,

Generally, we stores various values of choices in 1 column of database. For example, we store user’s preferred categories in user table’s. value stored in this field will be like this 1,2,12,22,4

1,12 and 2,22 similar pattern. it would be difficult to get user who preferred category would be either “1” or “2”. If you use “LIKE” query “category LIKE ‘%1%’” then it will return the result with 1, 212, 111 or others. But if you using using PHP explode it need loan all the record and check the array.

There is one of the functions from MySQL which help to solve this problem. It is available (as a substring) within a string. String contain comma separated characters or values.

It will returns 0 when search string does not exist in the string.

FIND_IN_SET(str, strlist)
SELECT FIND_IN_SET('1', '1,2,12,22,4')
The above statement finds the search string ‘1’ at 1st place within the string list. So it returns 1.

So WHERE query would be similar to following:
SELECT name FROM users WHERE FIND_IN_SET('1', category);
It will return record might have comma-separated values like ‘4,1,7,8’ or ‘1,11’ or ‘1,4,8,11,’ or anything others else which have “1” inside the category field.

Make a Comment