SQL Tutorial SQL Advanced SQL Database SQL References

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

value Required. Specify the value to find in the list.
val1, val2, val3, ... Required. Specify the list of values that need to be searched.

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:

EmpIDNameDepartment1Department2Department3
1JohnHRAdminTreasury
2MarryITFinance
3JoFinanceMarketing
4KimMarketingSales
5RameshITFinanceMarketing
6HuangITMarketingSales

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:

EmpIDNameDepartment1Department2Department3Result
1JohnHRAdminTreasuryNO
2MarryITFinanceYES
3JoFinanceMarketingYES
4KimMarketingSalesNO
5RameshITFinanceMarketingYES
6HuangITMarketingSalesNO

❮ MySQL Functions