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.

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:

1

2

3

FIND_IN_SET(find string,stringlist)

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:

1

2

3

SELECT FIND_IN_SET('b','a,b,c,d');

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.

NOTES:Mysqlstring function FIND_IN_SET can find only for one string in a set of strings. so you can’t user FIND_IN_SET like

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

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.

Post navigation

About Author

Bhumi

Bhumi shah is currently working with leading web development company as a Software Analyst and also the founder of the Creativedev. She has immense interest in programming and web designing.She is passionate about technical blogging and almost versatile in terms of programming across various languages & frameworks such as PHP,MySQL WordPress, Twitter Bootstrap, AngularJS, HTML5, CSS3, jQuery and more.

We absolutely love your blog and find most of your post’s to be just what I’m looking for. can you offer guest writers to write content for yourself? I wouldn’t mind producing a post or elaborating on a few of the subjects you write about here. Again, awesome weblog!

Wow, marvelous blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is wonderful, let alone the content!. Thanks For Your article about Mysql String Function FIND_IN_SET() | Creative Dev .

I just want to tell you that I am new to blogging and seriously liked you’re page. Likely I’m planning to bookmark your blog post . You amazingly have awesome well written articles. Many thanks for sharing your website.

Great post at Mysql String Function FIND_IN_SET() | Creative Dev. I was checking constantly this blog and I am impressed! Very useful information specifically the last part 🙂 I care for such information much. I was looking for this particular information for a very long time. Thank you and best of luck.