The FIELD() function returns the index position of a value in a list of values. The search is case-insensitive. If the value is not found, the result is 0. If the value is NULL, the result is 0.
MySQL FIELD Function Syntax
SQL
x
1
FIELD(value, val1, val2, val3, ...);MySQL FIELD Function Example
This query returns the index position of ‘q’ in the list ‘s’, ‘q’, ‘l’.
SQL
1
1
SELECT FIELD('q', 's', 'q', 'l');Note: The FIELD() function is supported in MySQL but not in SQLite. This example does not work in the TechKubo playground.
MySQL FIELD Function Parameters
| Parameter | Description |
|---|---|
| value | Required. The value to search for in the list. |
| val1, val2, val3, … | Required. The list of values to search in. |
MySQL FIELD Function Table Column Example
This query attempts to return the index position of ‘F’ in the list ‘M’, ‘F’, ‘O’, using the gender column from the Patients table.
SQL
1
1
SELECT FIELD('F', gender, 'M', 'F', 'O') AS GenderPosition2
FROM Patients;Note: The FIELD() function is supported in MySQL but not in SQLite. This example does not work in the TechKubo playground.
MySQL FIELD Function Notes
- Returns 0 if the value is not found.
- Returns 0 if the value is
NULL.- The first position in the list is 1, not 0.
- The search is case-insensitive.
- Works in MySQL 4.0 and higher.
- Not supported in SQLite. In SQLite, no direct equivalent exists.
