MySQL FIELD() Function

The MySQL FIELD() function returns the index position of a value in a list of values (val1, val2, val3, ...). In the special case it returns the following:

• If value is not found in the list of values (val1, val2, val3, ...), the function returns 0.
• If value is NULL, the function returns 0.
• If all arguments used with this function are string values, the find is performed as string values.
• If all arguments used with this function are numeric values, the find is performed as numeric values.

Syntax

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

Parameters

Return Value

Returns the index position of a value in a list of values (val1, val2, val3, ...).

Example 1:

The example below shows the usage of FIELD() function.

mysql> SELECT FIELD('b', 'a', 'b', 'c', 'd');
Result: 2

mysql> SELECT FIELD('b', 'A', 'B', 'C', 'D');
Result: 2

mysql> SELECT FIELD('B', 'a', 'b', 'c', 'd');
Result: 2

mysql> SELECT FIELD(3, 1, 2, 3, 4);
Result: 3

mysql> SELECT FIELD('z', 'a', 'b', 'c', 'd');
Result: 0

mysql> SELECT FIELD('a', '');
Result: 0

mysql> SELECT FIELD('a', NULL);
Result: 0

mysql> SELECT FIELD('C', 'A', NULL, 'C', 'D');
Result: 3

mysql> SELECT FIELD(NULL, 'A', 'B', 'C', 'D');
Result: 0

mysql> SELECT FIELD(NULL, 'A', NULL, 'C', 'D');
Result: 0

Example 2:

Consider a database table called Employee with the following records:

The statement given below can be used to find out the employee names of 'Finance' department.

SELECT *, 
IF(FIELD('Finance', Department1, Department2, Department3), 'YES', 'NO') AS Result 
FROM Employee;

This will produce the result as shown below: