How ELT() Works in MariaDB

In MariaDB, ELT() is a built-in string function that accepts a numeric argument, followed by a series of string arguments. It then returns the string that corresponds to the given numeric position provided by the first argument.

Syntax

The syntax goes like this:

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

Where N is the numeric argument, and str1[, str2, str3,…] represents the string arguments.

Example

Here’s a basic example:

SELECT ELT(2, 'Red', 'Green', 'Blue');

Result:

+--------------------------------+
| ELT(2, 'Red', 'Green', 'Blue') |
+--------------------------------+
| Green                          |
+--------------------------------+

In this case, we used 2 to return the second string argument.

Floats

If the first argument is a FLOAT, MariaDB rounds it to the nearest integer:

SELECT 
    ELT(2.4, 'Red', 'Green', 'Blue') AS "2.4",
    ELT(2.5, 'Red', 'Green', 'Blue') AS "2.5";

Result:

+-------+------+
| 2.4   | 2.5  |
+-------+------+
| Green | Blue |
+-------+------+

Specifying an Out of Range Position

Specifying an out of range position results in null being returned. Examples below.

Position of Zero

Providing 0 as the first argument returns null:

SELECT ELT(0, 'Red', 'Green', 'Blue');

Result:

+--------------------------------+
| ELT(0, 'Red', 'Green', 'Blue') |
+--------------------------------+
| NULL                           |
+--------------------------------+

Negative Position

Providing a negative value as the first argument returns null:

SELECT ELT(-2, 'Red', 'Green', 'Blue');

Result:

+---------------------------------+
| ELT(-2, 'Red', 'Green', 'Blue') |
+---------------------------------+
| NULL                            |
+---------------------------------+

When the Position is Too Large

If the first argument is a number that’s greater than the total number of string arguments, ELT() returns null:

SELECT ELT(20, 'Red', 'Green', 'Blue');

Result:

+---------------------------------+
| ELT(20, 'Red', 'Green', 'Blue') |
+---------------------------------+
| NULL                            |
+---------------------------------+

Non-Numeric Positions

If the first argument is not a number, ELT() returns null:

SELECT ELT('Two', 'Red', 'Green', 'Blue');

Result:

+------------------------------------+
| ELT('Two', 'Red', 'Green', 'Blue') |
+------------------------------------+
| NULL                               |
+------------------------------------+

Providing Just One String Argument

Providing a single string argument is valid, although in this case the first argument would need to be 1 to avoid getting null:

SELECT ELT(1, 'Red');

Result:

+---------------+
| ELT(1, 'Red') |
+---------------+
| Red           |
+---------------+

Null Strings

String arguments can be null without affecting the outcome of the others:

SELECT ELT(3, 'Red', null, 'Blue');

Result:

+-----------------------------+
| ELT(3, 'Red', null, 'Blue') |
+-----------------------------+
| Blue                        |
+-----------------------------+

Although, specifying a number that matches the null string argument will obviously return null:

SELECT ELT(2, 'Red', null, 'Blue');

Result:

+-----------------------------+
| ELT(2, 'Red', null, 'Blue') |
+-----------------------------+
| NULL                        |
+-----------------------------+

Specifying a Null Position

Providing null as the first argument results in null:

SELECT ELT(null, 'Red');

Result:

+------------------+
| ELT(null, 'Red') |
+------------------+
| NULL             |
+------------------+

Single Argument

Providing just one argument returns an error:

SELECT ELT(2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ELT'

Missing Argument

Calling ELT() without passing any arguments results in an error:

SELECT ELT();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ELT'