MySQL includes a FIELD() function and a FIND_IN_SET() function that both return the position of a string within a list. However, these functions work slightly differently to each other.

The main difference between these two functions is this:

• FIND_IN_SET() returns the index position of a string within a string list.
• FIELD() returns the index position of a string within a list of arguments.

So one function searches a string list, and the other function searches a list of arguments.

Syntax

First let’s look at the syntax of the two functions:

FIELD()

The syntax for the FIELD() function goes like this:

FIELD(str,str1,str2,str3,...)

This consists of an initial argument, followed by multiple subsequent arguments. The subsequent arguments are what get searched – however many of them there are.

FIND_IN_SET()

The syntax for the FIND_IN_SET() function goes like this:

FIND_IN_SET(str,strlist)

This consists of an initial argument, followed by one more argument. The second argument contains a list of strings to be searched.

Examples

FIELD()

Here’s an example of the FIELD() function in action:

SELECT FIELD('horse', 'Cat','Dog','Horse') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+

FIND_IN_SET()

And here’s how the FIND_IN_SET() function would be constructed to get the same result:

SELECT FIND_IN_SET('horse', 'Cat,Dog,Horse') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+