Mysql String Function FIND_IN_SET( )

Before someday, I have used MySQL function for fetching category from some the categories and I really like this function.It reduces the use of some complex queries.You should also like New Features in MySQL 5.6.

One of the very important 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.

[sociallocker]

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

FIND_IN_SET is useful when you are using explode OR implode to store multiple values.

If Data stored in the database with comma separated value like you have cat_id and you stored it as (1,2,3,5 ) within one field , you can use FIND_IN_SET in select query to match particular value in whole fieldset.

SYNTAX:

It returns a value from 1 to N depends on the position of the string in stringlist, if the string is in the stringlist, consisting of N substrings. A string list is a string composed of substrings separated by the comma.If the first argument is a constant string and the second is a column type SET, the function FIND_IN_SET () is optimized to use bit arithmetic.If the string is not listed in stringlist or if stringlist is an empty string, It will return 0.Also, If one of the arguments is NULL, it returns 0.

Example:

This will fetch the number of characters in the set.Above example, ‘b’ is the (single character)string used for the find.This function will not work properly if the first argument contains a comma.

If you have a column with concatenated data,It is good to go with FIND_IN_SET() function rather than MySQL IN() Function.

Must Read:

MySQL coalesce function
Transaction in MySQL
MySQL CURSOR Explained

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

Have you ever used FiND_IN_SET in your queries of MySQL,play with the FiND_IN_SET () function and Share how FiND_IN_SET worked for you.
[/sociallocker]