MySQL FIND_IN_SET Function

MySQL FIND_IN_SET function is one of the String Functions, which is useful to find the position of a substring within a string list (N number of strings separated by comma) and returns those positions.

Let me show you how to write the FIND_IN_SET Function query to find and return the position of a string with an example, and the basic syntax of it is as shown below:

FIND_IN_SET(Str, String_List)

MySQL FIND_IN_SET Function Example

The Find in Set method finds the position of the first argument inside a second argument and returns that position as an output. The following query shows multiple ways to use this FIND_IN_SET Function.

SELECT FIND_IN_SET('c', 'a,b,c,d,e,f') r1;

SELECT FIND_IN_SET('e', 'a,b,c,d,e,f') r2;

SELECT FIND_IN_SET('x', 'a,b,c,d,e,f') r3;

+----+----+----+
| r1 | r2 | r3 |
+----+----+----+
|  3 |  5 |  0 |
+----+----+----+

In this MySQL example, we are using the string values and NULL as the first argument.

SELECT FIND_IN_SET('abc', 'an,ball,abc,dog,em,fish') a;

SELECT FIND_IN_SET('em', 'an,ball,abc,dog,em,fish') b;

SELECT FIND_IN_SET(NULL, 'an,ball,abc,dog,em,fish') c;

+---+---+------+
| a | b | c    |
+---+---+------+
| 3 | 5 | NULL |
+---+---+------+