MySQL FIND_IN_SET function

techie-iconThe MySQL FIND_IN_SET function is a cheeky little number. Essentially what it will do is return the position of a value within a set of comma separated values.

The basic syntax is: FIND_IN_SET(str,strlist)

Where str is the value you want to find and strlist is the set of comma separated values.

So, for example FIND_IN_SET(‘3′,’7,2,8,11,3,5,6’) would return 5 because 3 is the 5th entry in the second parameter list. If the string you’re searching for isn’t in the list at all it will return 0.

This is the sort of thing that would be quite handy for a list of blog tags for example.

Let’s imagine you had a tags table that simple equated a tag number to a tag name and then in your main blog table you stored the tag numbers for each blog a bit like this:

title tags
Blog 1 1,2,3
Blog 2 2
Blog 3 4,5
Blog 4 3
Blog 5 2,4,5

If you now wanted to find any blogs that have a tag of 2, you could run the following SQL:

SELECT * FROM blog WHERE FIND_IN_SET('2', tags)

And it should return:

title tags
Blog 1 1,2,3
Blog 2 2
Blog 5 2,4,5

There are probably better ways to maintain a list of tags (with a bit more database planning) but FIND_IN_SET can be a useful little function.

Leave a Reply