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'