find_in_set() – MySQL function – comma sperated search

Generally, We stores various values of choices in same a column of mysql database. For example we store user’s preferred categories in user table’s preferred_categories(varchar(250)) field(column). Value stored in this field may be like 1,2,5,6,1

1,18 or any of similar pattern. It would be difficult to get all user whose preferred category would be “1″. If you use like query “preferred_categories LIKE ’1%’” then it also get matched with 1 & 11.

There is one of the good functions from MySQL which help to solve this problem. FIND_IN_SET() function is use to match among comma separated values. Basically FIND_IN_SET() function is use with SET type of datatype but it’s compatible to use with any other datatype where values get stored as comma separated.

mysql string function is FIND_IN_SET and its returns the position of a string value if it is available (as a substring) within a string. String contain comma separated characters or values.

This function returns 0 when search string does not exist in the string.

SYNTAX:

   SELECT FIND_IN_SET('1',preferred_categories);

Above syntax, ‘1’ is the string/value used for find within preferred_categories.

So Where query would be similar to following:

   SELECT cat_name FROM category WHERE FIND_IN_SET('1',preferred_categories);

Above query will give you categories which are having preferred_categories 1 along with or without other categories.In table records might have comma-separated values like ’4,5,7,8′or ’1,11′ or ’1,4,8,11,′ anything. So above expression in WHERE will return value greater than “zero” (0) and that row will be returned in result.

Keep Querying 

原文地址:  http://www.bytestechnolab.com/blog/2011/12/05/find_in_set-mysql-function-comma-sperated-search/