Blog

In this guide, we will explain 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 is:

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

Parameters or Arguments

valueThe 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 :

• 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.

For example:

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

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

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

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

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

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

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

Next Topic : Click Here