Back to Top

Mysql String Function FIND_IN_SET( )

findinset in mysql

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.

NOTES:Mysql string 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.

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]

Comments (19)

  1. What will the function return if the string is to be found at the first position? 0 or 1? If it’s 0 then how do you distinguish it from the 0 where nothing is found?

    1. It will return 1 for first match, execute below queries and you will get your answer.

      SELECT FIND_IN_SET('4','4,5,6,7');
      SELECT FIND_IN_SET('4','5,4,6,7');

      1. Good job mikang it appear easy.

      2. You know what, I’m very much ilnicned to agree.

    2. YMMD with that ansewr! TX

      1. The forum is a brhgiter place thanks to your posts. Thanks!

  2. This artlice keeps it real, no doubt.

    1. Heck of a job there, it aobsluetly helps me out.

      1. Hey! I just wish to give a huge tubmhs up for the nice data you have got right here on this post. I might be coming back to your weblog for more soon.

  3. Could you write about Physics so I can pass Scincee class?

    1. Great isnihgt. Relieved I’m on the same side as you.

  4. I smlipy couldn’t depart your website before suggesting that I really loved the usual information an individual supply on your visitors? Is gonna be again continuously to inspect new posts.

  5. 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!

  6. Kristeen Kroninger says:

    Respect to the post author. This is truly some interesting information.

  7. 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 .

  8. Good one particular weblog operator triumph webpage publish wonderful sharings in such a blog site always have fun

  9. 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.

  10. 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.

  11. I am using

    SELECT p.* FROM products AS p
    JOIN cats AS c
    ON p.cat=12 AND FIND_IN_SET(123, c.prId_relate)=0

    // run Not match records

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to use Index in MySQL

Posted on 8 years ago

Bhumi

GoTo statement in PHP

Posted on 12 years ago

Bhumi

The Readers’ Poll – November 2012

Posted on 11 years ago

Bhumi