How to Use ELT Function in MySQL: Syntax and Examples

In MySQL, the ELT function returns the string at the given index from a list of strings. You provide an index number as the first argument and then a list of strings; ELT returns the string at that position or NULL if the index is out of range.

Syntax

The ELT function syntax is:

• ELT(N, str1, str2, ...)

Where:

• N is the index number (1-based) indicating which string to return.
• str1, str2, ... are the strings to choose from.

If N is less than 1 or greater than the number of strings, ELT returns NULL.

sql

ELT(N, str1, str2, str3, ...)

Example

This example shows how ELT returns the string at the given index:

sql

SELECT ELT(2, 'apple', 'banana', 'cherry') AS fruit;
SELECT ELT(4, 'red', 'green', 'blue') AS color;
SELECT ELT(1, 'cat', 'dog', 'bird') AS animal;

Output

fruit
banana

color
NULL

animal
cat

Common Pitfalls

Common mistakes when using ELT include:

• Using an index less than 1 or greater than the number of strings returns NULL, which might be unexpected.
• Passing a non-integer or NULL as the index will cause ELT to return NULL.
• Confusing ELT with FIELD, which works differently.

sql

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

/* Right: index starts at 1 */
SELECT ELT(1, 'a', 'b', 'c') AS result_right;

Output

result_wrong
NULL

result_right
a

Quick Reference

Parameter	Description
N	Index number (1-based) to select string
str1, str2, ...	List of strings to choose from
Return	String at position N or NULL if out of range

Key Takeaways

ELT returns the string at the given 1-based index from a list of strings.

If the index is out of range or invalid, ELT returns NULL.

Index numbering starts at 1, not 0.

ELT is useful for selecting a value from a fixed list based on a number.

Do not confuse ELT with FIELD; they serve different purposes.