Mysql FIELD - How to Return an Argument’s Position within a List of Arguments in MySQL

In MySQL, you can use the FIELD() function to return the position of a given string or number within a list of arguments. The function returns the index (position) of the first argument in the list of subsequent arguments.

The syntax goes like this:

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

Where str is the item you want to find, and str1,str2,str3,... is the list you’re searching through.

Here’s an example:

SELECT FIELD('Homer', 'Marge', 'Homer', 'Bart') AS 'Where is Homer?';

Result:

+-----------------+
| Where is Homer? |
+-----------------+
|               2 |
+-----------------+

In this example, the list is: 'Marge', 'Homer', 'Bart' and we’re searching for the string Homer within that list. And because Homer is the 2nd item in the list of arguments, the result is 2.

Exact Match

Note that it must be an exact match, otherwise it will return 0.

For example, if we drop the r from Homer, we end up searching for Home instead:

SELECT FIELD('Home', 'Marge', 'Homer', 'Bart') AS 'Where is Home?';

Result:

+-----------------+
| Where is Home? |
+-----------------+
|               0 |
+-----------------+

In this case, even though Homer contains Home, it’s not an exact match and the result is 0.

 Numbers

The FIELD() function also works with numbers:

SELECT FIELD(1, 3, 2, 1) AS 'Where is 1?';

Result:

+-------------+
| Where is 1? |
+-------------+
|           3 |
+-------------+

When all arguments to FIELD() are numbers, all arguments are compared as numbers. If all are strings, they’re compared as strings. If they’re mixed, they’re compared as double.

The FIELD() function is a complement to the ELT() function, which allows you to find a list item by its position in the list.

Also see How to Return the Position of a List Item using the FIND_IN_SET() function.