MySQL FIELD() function

In this context, we will learn how we can use the MySQL FIELD() function with proper syntax and good examples.

Introduction of MySQL FIELD() function

The index position of a mentioned value in a list of given values is derived with the help of this function. For example, if the given list is ("1", "3", "2") and the value is "2" for which index position is going to be searched, then this function will return 3 as the index position.

Some important Features of the MySQL FIELD() function:

• The index position of a mentioned value in a list of given values is found with the help of this function.
• Two types of parameters are accepted by this function.
• The first parameter is a specified value to search for the index, and the second parameter is the mentioned list of values to search for.
• This function does a case-insensitive search.
• This function will return 0 when the given value is not present in the specified list of given values,

Syntax of the MySQL FIELD() function

The syntax of the MySQL FIELD() function is given as follows:

FIELD(value, value1, value2, value3, ...)

Parameters or arguments used in MySQL FIELD() function:

There is two parameters accepted by the FIELD() function in MySQL, which are given as follows:

Value: Specified value to search for.

value1, value2, value3, …: these are the mentioned list of values to search.

Returns:

It will return the index position of a mentioned value in a list of given values.

Application used for FIELD() function:

The FIELD() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL FIELD() function:

Now we will look into some MySQL FIELD() function examples and will explore how we can use the FIELD function in MySQL.

Example-1 :

Here, we will derive the index position 4 of the specified value 8 in the list of ("1", "3", "6", "8","7") with the help of the FIELD function in MySQL.

SELECT FIELD("1", "3", "6", "8","7");

Output :

4
1 row in set (0.00 sec)

Example-2 :

Here, we will derive the index position 1 of the specified value 2 in the list of ("2", "7", "3", "1", "5"). Here in the list, the value 5 is repeated two times, but the function returns the index position of the first position only.

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

Output :

1
1 row in set (0.00 sec)

Example-3 :

Here, we will derive the index position 2 of the specified value "a" in the list of ("b," "A," "a," "c"). Here alphabets "A" and "a" are used in the list, but this function returns the index position of "A" for value "a" this happens because this function performs a case-insensitive search.

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

Output :

2
1 row in set (0.00 sec)

Example-4 :

Here, we will derive 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
1 row in set (0.00 sec)

Application:

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

Summary:

In the above context, we have learned how we can use the FIELD() function in MySQL used to return the index position of a mentioned value in a list of given values.