MySQL FIELD() Function

In MySQL, the FIELD() function return the position of a specified value in a given list.

FIELD() Syntax

Here is the syntax of MySQL FIELD() function:

FIELD(value, v1, v2, ..., vN)

Parameter Description

value

Required. The value to look for in the list.

v1, v2, ..., vN

Required. The list.

Return value

The MySQL FIELD() function returns a number value which is the position of the specifed value in the given list.

• If the list includes the specified value, the FIELD() function returns the corresponding position number. The value of the position number starts at 1.
• If the list does not include the specified value, the FIELD() function returns 0.
• If value is NULL, the FIELD() function returns 0.

FIELD() Examples

Simple Examples

• If you need to the position of 'A' int the 'A', 'B', 'C' list, you can use the following statement:

  SELECT FIELD('A', 'A', 'B', 'C');
  

  +---------------------------+
  | FIELD('A', 'A', 'B', 'C') |
  +---------------------------+
  |                         1 |
  +---------------------------+

  Here, 'A' is the first element in the 'A', 'B', 'C' list, so the result is 1.

• If the specified value is not found in the list, the FIELD() function returns 0.

  SELECT FIELD('D', 'A', 'B', 'C');
  

  +---------------------------+
  | FIELD('D', 'A', 'B', 'C') |
  +---------------------------+
  |                         0 |
  +---------------------------+

• If the value is NULL, the FIELD() function returns 0.

  SELECT FIELD(NULL, 'A', 'B', NULL);
  

  +-----------------------------+
  | FIELD(NULL, 'A', 'B', NULL) |
  +-----------------------------+
  |                           0 |
  +-----------------------------+

use FIELD() sort

In some cases, you need the data in a table to be sorted by a limited number of state values, you can use the FIELD() function. For example, there is a table named film that has a rating column and the column has a limited number of values: 'G', 'PG', 'PG-13', 'R', 'NC-17'. If you want to sort by rating column in ascending order, you can use the following statement:

SELECT *
FROM film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

For the detail, please go to MySQL ORDER BY tutorial .

FIELD() vs CASE

You can use CASE statement replace the FIELD() function. For example, the following statement

SELECT FIELD('A', 'A', 'B', 'C') a_index;

is equal to

SELECT CASE 'A'
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'C' THEN 3
       END a_index;

There is no doubt that the FIELD() function is short, clean and can be modified easily.