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.

Mysql: Select values that have at least ID from array

We have a table film_actor that represents many to many relationship between films and actors.

CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the actors from a given array { 5, 8, 10 } were starring:

SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0;
+---------+
| film_id |
+---------+
|       1 |
|       3 |
+---------+

The next query will return the same films:

SELECT film_id, GROUP_CONCAT(actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  FIND_IN_SET(5, actors) AND FIND_IN_SET(8, actors) AND FIND_IN_SET(10, actors);
+---------+----------+
| film_id | actors   |
+---------+----------+
|       1 | 5,6,8,10 |
|       3 | 5,8,10   |
+---------+----------+