FIELD() Function in MySQL

FIELD() function :
This function in MySQL is used to return the index position of a specified value in a list of given values. For example, if the given list is (“3”, “1”, “2”) and the value is “1” for which index position is going to be search, then this function will return 2 as the index position.

Features :

• This function is used to find the index position of a given value in a list of specified values.
• This function accepts two types of parameters.
• First parameter is specified value to search for and second parameter is the specified list of values to search.
• This function does a case-insensitive search.
• If the given value is not present in the specified list of given values, this function returns 0.

Syntax :

FIELD(value, val1, val2, val3, ...)

Parameter :
This method accepts two parameters as given below :

• value : Specified value to search for.
• val1, val2, val3, … : Specified list of values to search.

Returns :
It return the index position of a specified value in a list of given values.

Example-1 :
Getting the index position 3 of the specified value 5 in the list of (“1”, “3”, “5”, “7”).

SELECT FIELD("5", "1", "3", "5", "7");

Output :

3

Example-2 :
Getting the index position 2 of the specified value 5 in the list of (“2”, “5”, “5”, “0”). Here in the list the value 5 is repeated two times but the function returns the index position of first time used value position only.

SELECT FIELD("5", "2", "5", "5", "0");

Output :

2

Example-3 :
Getting the index position 2 of the specified value “a” in the list of (“b”, “A”, “a”, “c”). Here alphabets “A” and “a” is used in the list but this function returns the index position of “A” for value “a” this happens because this function performs case-insensitive search.

SELECT FIELD("a", "b", "A", "a", "c");

Output :

2

Example-4 :
Getting the index position 3 of the specified value 4 in the numeric list of (0, 2, 4, 6, 8).

SELECT FIELD(4, 0, 2, 4, 6, 8);

Output :

3

Application :
This function is used to return the index position of a specified value in a list of given values.