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.

Vishesh is currently working as an Intermediate Software Engineer with Orion Health, New Zealand. He graduated with a Masters in Information Technology from the University of Auckland in 2021. With more than 4 years of work experience, his expertise includes Java, Python, Machine Learning, PHP, Databases, Design and Architecture.