MariaDB FIELD() Function

In MariaDB, the FIELD() function returns the position index of a specified value in a given list.

MariaDB FIELD() Syntax

Here is the syntax of the MariaDB FIELD() function:

FIELD(value, v1[, v2, ..., vN])

Parameters

value

Required. The value to look up in the list.

v1[, v2, ..., vN]

Required. Elements list being searched.

If you provide no parameter or provide one parameter, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FIELD'.

Return value

If the specified value is found in the list, the FIELD() function returns the corresponding position index. The value of the index returned by the FIELD() function starts 1.

If the specified value is not found in the list, the FIELD() function returns 0.

The FIELD() function returns 0 if the value to be looked up is NULL.

MariaDB FIELD() Examples

If you need to know the position of 'A' in the list 'A', 'B', 'C', you can use the following statement:

SELECT FIELD('A', 'A', 'B', 'C');

Output:

+---------------------------+
| FIELD('A', 'A', 'B', 'C') |
+---------------------------+
|                         1 |
+---------------------------+

Here, 'A', 'B', 'C' is a list, and 'A' is in position 1, so it is returned 1.

MariaDB FIELD() is case-insensitive, as follows:

SELECT FIELD('a', 'A', 'B', 'C');

Output:

+---------------------------+
| FIELD('a', 'A', 'B', 'C') |
+---------------------------+
|                         1 |
+---------------------------+

MariaDB FIELD() can search the position index of a number from a mumber list, as follows:

SELECT FIELD(1, 2, 3, 1);

Output:

+-------------------+
| FIELD(1, 2, 3, 1) |
+-------------------+
|                 3 |
+-------------------+

Order by FIELD()

You may need to sort according to the order of the values ​​in a certain list, for example, if you need to sort rows by the order of Green, Red, Blue, you can use the FIELD() function.

The following statement simulates your needs:

SELECT *
FROM product
ORDER BY FIELD(color, 'Green', 'Red', 'Blue');

FIELD() vs CASE...WHEN

The FIELD() function can be implemented by CASE...WHEN, such as the following statement:

SELECT FIELD('A', 'A', 'B', 'C') a_index;

can be rewritten as:

SELECT CASE 'A'
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'C' THEN 3
       END a_index;

There is no doubt that the statement with FIELD() is cleaner, less error-prone, and easier to modify.

Conclusion

In MariaDB, the FIELD() function returns the position index of a specified value in a given list.