Mysql FIND_IN_SET - How to Return the Position of a List Item in MySQL

In MySQL, you can use the FIND_IN_SET() function to return the index of a given list item within a string list (for example ‘item1, item2, item3,…’).

The function takes two arguments; the string to find, and the list to search.

The syntax goes like this:

FIND_IN_SET(str,strlist)

Where str is the the string you’re looking for, and strlist is the string list to search through.

Example

Here’s an example:

SELECT FIND_IN_SET('Dog', 'Cat,Dog,Horse') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+

This returns 2 because that’s the position of the string Dog within the string list.

Note that only the index of the first occurrence is returned. So if there was another occurrence of Dog after that first one, we’d still get the same result:

SELECT FIND_IN_SET('Dog', 'Cat,Dog,Horse,Dog') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+

No Matches

When the string isn’t found, a result of 0 is returned:

SELECT FIND_IN_SET('Lizard', 'Cat,Dog,Horse') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

What if the First Argument contains a Comma?

The function won’t return a positive result if the first argument contains a comma.

Here’s an example:

SELECT FIND_IN_SET('Cat,Dog', 'Cat,Dog,Horse') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

In this example, one might expect it to return a positive result (given Cat,Dog is actually in the list), however, the comma is used as a separator in the list, and therefore, the string match doesn’t work.

Also see How to Return an Argument’s Position within a List of Arguments by using the FIELD() function.