MySQL FIELD() Function
The FIELD() function in MySQL searches for a specific value within a list of values and returns the position (index) of the first match. This is a very handy function for working with lists and determining the location of a specific item.
FIELD(): Definition and Usage
FIELD() is useful for finding the position of a value in a set. It returns the index of the first match it finds in the list; indexes start at 1. If the value isn't found, the function returns 0. The search performed by FIELD() is case-insensitive.
Syntax
Syntax
FIELD(value, val1, val2, val3, ...)
Parameter Values
| Parameter | Description |
|---|---|
value |
The value to search for within the list. This is required. |
val1, val2, val3,... |
The list of values to search. At least one value is required. |
Examples
Finding the Position of a String
This example finds the position of "q" within the list ("s", "q", "l").
Syntax
SELECT FIELD("q", "s", "q", "l");
Output
2
More Examples
These examples demonstrate various uses of the FIELD() function.
Syntax
SELECT FIELD("c", "a", "b"); -- "c" is not found, returns 0.
SELECT FIELD("Q", "s", "q", "l"); -- Case-insensitive match.
SELECT FIELD(5, 0, 1, 2, 3, 4, 5); --Numeric list; returns position of 5.
Output
0
2
6