MariaDB FIELD() vs FIND_IN_SET(): What’s the Difference?

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).