Search comma separated db column mysql

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,11,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:

1
SELECT FIND_IN_SET('1',preferred_categories);

Leave a Reply