Generally, We give the various options of choices to choose on websites in form of checkbox and stores various values of choices in a column of MySQL database as a comma seperated . For example we store user’s preferred hobby in user table’s hobbies(varchar(250)) field(column). Value stored in this field may be like 1,2,5,6,1 or may be ‘sports’,’gardening’,’cooking’,’reading’. Now sometimes we need to get the records which match with the given string in the field column. we can do it by using programming language also but finding values in comma separated string using mysql is easy.
MySQL provides a good function to find values in comma separated string field.
FIND_IN_SET()
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.
FIND_IN_SET() : returns the position of a string value if it is available (as a substring) within a string. It returns 0 when search string does not exist in the string.
SYNTAX :
1 | FIND_IN_SET(searchstring,commaseperatedstring) |
Here searchstring is string to be searched eg: ‘gardening’ or ‘gardening’,’sports’
commaseparatedstring is comma separated string or field in mysql table
Example:
1.
1 2 3 | SELECT FIND_IN_SET('gardening',hobbies); // hobbies is the field name which has comma separated string |
2.
1 2 3 4 | SELECT username FROM tblUser WHERE FIND_IN_SET('sports',hobbies) > 0; Output // it will return the name of the user who has sports as his hobbies |
awesome !!