How to Use ELT and FIELD Functions in MySQL

In MySQL, ELT returns the string at a given index from a list of strings, while FIELD returns the position of a value within a list. Use ELT(index, str1, str2, ...) to get the string at index, and FIELD(value, val1, val2, ...) to find the position of value in the list.

Syntax

ELT returns the string at the specified index from a list of strings. Index starts at 1.

FIELD returns the position of a value in a list of values. If not found, it returns 0.

sql

ELT(index, str1, str2, str3, ...)
FIELD(value, val1, val2, val3, ...)

Example

This example shows how ELT returns the string at a given position and how FIELD finds the position of a value in a list.

sql

SELECT ELT(2, 'apple', 'banana', 'cherry') AS fruit_at_2,
       FIELD('banana', 'apple', 'banana', 'cherry') AS position_of_banana,
       FIELD('orange', 'apple', 'banana', 'cherry') AS position_of_orange;

Output

fruit_at_2 | position_of_banana | position_of_orange
-----------|--------------------|-------------------
banana     | 2                  | 0

Common Pitfalls

• Remember that ELT index starts at 1, not 0. Using 0 or negative numbers returns NULL.
• FIELD returns 0 if the value is not found, which can be confused with a valid position if not checked.
• Both functions treat NULL values specially: FIELD returns 0 if the value is NULL or the list contains NULL.

sql

/* Wrong: ELT index 0 returns NULL */
SELECT ELT(0, 'a', 'b', 'c') AS wrong_index;

/* Right: ELT index 1 returns first string */
SELECT ELT(1, 'a', 'b', 'c') AS correct_index;

Output

wrong_index
-----------
NULL

correct_index
-------------
a

Quick Reference

Function	Purpose	Returns	Notes
ELT(index, str1, str2, ...)	Returns string at position index	String or NULL if index invalid	Index starts at 1
FIELD(value, val1, val2, ...)	Returns position of value in list	Position (1-based) or 0 if not found	0 means value not found

Key Takeaways

Use ELT to get a string by its 1-based position from a list of strings.

Use FIELD to find the position of a value in a list; it returns 0 if not found.

ELT index must be 1 or higher; 0 or negative returns NULL.

FIELD returns 0 for missing values, so check for zero to detect absence.

Both functions are useful for mapping indexes to values and vice versa in queries.