MySQL FIELD() Function

 The FIELD() function in MySQL is used to return the position of a value within a list of specified values. It is particularly useful for custom sorting and ranking.

Syntax

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

Parameters

• value: The value to search for.
• val1, val2, val3, ...: The list of values to search within.

Return Value

• The position (1-based index) of value within the list of values.
• Returns 0 if the value is not found in the list.
• Returns NULL if the value is NULL.

Examples

Example 1: Basic Usage

SELECT FIELD('banana', 'apple', 'banana', 'cherry') AS position;

Result: 2
Explanation: 'banana' is the second value in the list.

Example 2: Value Not Found

SELECT FIELD('grape', 'apple', 'banana', 'cherry') AS position;

Result: 0
Explanation: 'grape' is not in the list.

Example 3: With NULL Value

SELECT FIELD(NULL, 'apple', 'banana', 'cherry') AS position;

Result: NULL
Explanation: The input value is NULL.

Example 4: Using FIELD() for Custom Sorting

SELECT name
FROM fruits
ORDER BY FIELD(name, 'banana', 'apple', 'cherry');

This query sorts the fruits table such that 'banana' comes first, followed by 'apple', and then 'cherry'. Any other values will appear after these in the default order.

Notes

• FIELD() is 1-based, meaning the first position in the list is 1, not 0.
• It's commonly used in conjunction with ORDER BY to create custom sorting orders.