Two similar functions in MariaDB are FIELD() and FIND_IN_SET(). At first glance, these appear to do the same thing, but there is a difference.
This article looks at the difference between these two functions.
Syntax and Definitions
First, let’s look at the syntax and definition of each function:
| Function | Syntax | Definition |
|---|---|---|
FIELD() | FIELD(pattern, str1[,str2,...]) | Returns the index position of the string or number matching the given pattern. |
FIND_IN_SET() | FIND_IN_SET(pattern, strlist) | Returns the index position where the given pattern occurs in a string list. |
Both functions accept a pattern as their first argument. However, it’s what comes next that highlights their differences:
FIELD()accepts one or more strings, separated by a comma.FIND_IN_SET()accepts a single string list.
Example
Here’s an example that demonstrates the difference between these two functions:
SELECT
FIELD('bat', 'cat','dog','bat') AS "FIELD()",
FIND_IN_SET('bat', 'cat,dog,bat') AS "FIND_IN_SET()";Result:
+---------+---------------+ | FIELD() | FIND_IN_SET() | +---------+---------------+ | 3 | 3 | +---------+---------------+
Each function returned the same result, but each function accepted the strings in a different format.
The values provided to FIELD() came as three separate arguments, whereas the values provided to FIND_IN_SET() came as a single string list (which contained the separate values).