MySQL FIELD Function | Get Position of Value in a List

MySQL FIELD is an inbuilt function in MySQL. It returns the index position of a value in a list of values. It compares the position of a value in a list of remaining values in a case-insensitive search. In this article, we will discuss the MySQL FIELD Function. Also, we will discuss a few examples of using it.

Note: The positions start from 1 and not 0.

Syntax

FIELD (searchValue, listValue1, listValue2, … listValueN)

Parameters

You can pass any number of parameters to the function. However, atleast two parameters are mandatory. The description of the parameters is as follows:

• searchValue: The first value to the function is the string to search for.
• listValue1, listValue2, … listValueN: The additional parameters specify the list of values to search in. Also, you can pass any number of parameters to the function.

Return Value

The function returns the index position of the search value in the list of values. However, if the value does not exist in the list, the function returns 0. Also, it returns 0 if the value is NULL.

Examples

Let’s discuss a few examples of using the function.

Example 1: Simple Search

mysql> SELECT FIELD("a", "x", "a", "y") as position;
+----------+
| position |
+----------+
|        2 |
+----------+

In the above example, the MySQL FIELD Function returns the index position of the value “a” in the list “x, a, y”. The function returns 2 as the position.

Example 2: Value Not Found

For instance, consider an example when the search value does not exist in the list of values.

mysql> SELECT FIELD("abc", "xyz", "pqr", "def", "a", "b", "c") as position;
+----------+
| position |
+----------+
|        0 |
+----------+

When the search value does not exist in the list of values, the MySQL FIELD Function returns 0.

Example 3: Case-Insensitive Search

The function searches in a case-insensitive manner. Consider the following example:

mysql> SELECT FIELD("R", "p", "q", "r") as position;
+----------+
| position |
+----------+
|        3 |
+----------+

Example 4: Numeric Search

Similarly, you can also pass integer values instead of strings to the function.

mysql> SELECT FIELD(3, 1, 2, 3, 4, 5) as position;
+----------+
| position |
+----------+
|        3 |
+----------+

Conclusion

In conclusion, we discussed the MySQL FIELD Function. It returns the index position of a value in a list of values. You can read more about it on the Official MySQL Documentation. Additionally, you can read about more MySQL String Functions on Concatly.