MySQL: FIELD Function

This tutorial explains how to use the MySQL FIELD function with syntax and examples.

Description

The MySQL FIELD function returns the position of a value in a list of values (val1, val2, val3, ...).

Syntax

The syntax for the FIELD function in MySQL is:

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

Parameters or Arguments

value

The value to find in the list.

val1, val2, val3, ...

The list of values that is to be searched.

Note

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

Applies To

The FIELD function can be used in the following versions of MySQL:

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

Example

Let's look at some MySQL FIELD function examples and explore how to use the FIELD function in MySQL.

For example:

mysql> SELECT FIELD('b', 'a', 'b', 'c', 'd', 'e', 'f');
Output: 2

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

mysql> SELECT FIELD(15, 10, 20, 15, 40);
Output: 3

mysql> SELECT FIELD('c', 'a', 'b');
Output: 0

mysql> SELECT FIELD('g', '');
Output: 0

mysql> SELECT FIELD(null, 'a', 'b', 'c');
Output: 0

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