How FIND_IN_SET() Works in MariaDB

In MariaDB, FIND_IN_SET() is a built-in string function that returns the index position where the given pattern occurs in the specified string list.

It accepts two arguments. The first is the pattern you want to search for. The second argument is a string containing comma-separated values for which to match the pattern against.

Syntax

The syntax goes like this:

FIND_IN_SET(pattern, strlist)

Where pattern is the pattern you want to search for, and strlist represents the string of comma-separated values to search.

Example

Here’s a basic example:

SELECT FIND_IN_SET('Green', 'Red,Green,Blue');

Result:

+----------------------------------------+
| FIND_IN_SET('Green', 'Red,Green,Blue') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+

In this case, the pattern (Green) occurs at the second position, and so 2 is returned.

No Match

If there’s no match, 0 is returned.

Example:

SELECT FIND_IN_SET('Nope', 'Red,Green,Blue');

Result:

+---------------------------------------+
| FIND_IN_SET('Nope', 'Red,Green,Blue') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

Multiple Matches

If there are multiple matches, only the index of the first one is returned:

SELECT FIND_IN_SET('Dog', 'Cat,Dog,Cat,Dog,Cat');

Result:

+-------------------------------------------+
| FIND_IN_SET('Dog', 'Cat,Dog,Cat,Dog,Cat') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+

Specifying a Null Pattern

Providing null as the first argument results in null being returned:

SELECT FIND_IN_SET(null, 'Cat,Dog,Cat');

Result:

+----------------------------------+
| FIND_IN_SET(null, 'Cat,Dog,Cat') |
+----------------------------------+
|                             NULL |
+----------------------------------+

Specifying a Null String List

Providing null as the second argument also results in null:

SELECT FIND_IN_SET('Dog', null);

Result:

+--------------------------+
| FIND_IN_SET('Dog', null) |
+--------------------------+
|                     NULL |
+--------------------------+

Empty String List

If the string list is empty, FIND_IN_SET() returns 0:

SELECT FIND_IN_SET('Dog', '');

Result:

+------------------------+
| FIND_IN_SET('Dog', '') |
+------------------------+
|                      0 |
+------------------------+

Comma in the Pattern

FIND_IN_SET() does not return the correct result if the pattern contains a comma:

SELECT FIND_IN_SET('Green,', 'Red,Green,Blue');

Result:

+-----------------------------------------+
| FIND_IN_SET('Green,', 'Red,Green,Blue') |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

Single Argument

Passing just one argument to FIND_IN_SET() returns an error:

SELECT FIND_IN_SET('Cat');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FIND_IN_SET'

Missing Argument

Calling FIND_IN_SET() without passing any arguments results in an error:

SELECT FIND_IN_SET();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FIND_IN_SET'