SQL Tutorial SQL Advanced SQL Database SQL References

MySQL ELT() Function



The MySQL ELT() function returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. It returns NULL if N is less than 1 or greater than the number of the string specified as arguments. This function is the complement of FIELD() function.

Syntax

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

Parameters

N Required. Specify the index number.
str1, str2, str3,... Required. Specify the list of strings.

Return Value

Returns the Nth element of the list of strings.

Example 1:

The example below shows the usage of ELT() function.

mysql> SELECT ELT(1, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning'

mysql> SELECT ELT(2, 'Learning', 'MySQL', 'is', 'fun');
Result: 'MySQL'

mysql> SELECT ELT(5, 'Learning', 'MySQL', 'is', 'fun');
Result: NULL

mysql> SELECT ELT(0, 'Learning', 'MySQL', 'is', 'fun');
Result: NULL

mysql> SELECT ELT(4, 'Learning', 'MySQL', 'is', 'fun');
Result: 'fun'

mysql> SELECT ELT(3, 10, 20, 30, '40');
Result: 30

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpIDNameDateLoginTime
1John2019-10-2509:20:38
2Marry2019-10-2509:21:05
3Jo2019-10-2509:24:35
4Kim2019-10-2509:25:24
5Ramesh2019-10-2509:27:16

The following query can be used to get the 2nd element from list of strings specified by column records:

SELECT *, 
ELT(2, Name, Date, LoginTime) AS ELT_Value
FROM EmployeeLogin;

This will produce a result similar to:

EmpIDNameDateLoginTimeELT_Value
1John2019-10-2509:20:382019-10-25
2Marry2019-10-2509:21:052019-10-25
3Jo2019-10-2509:24:352019-10-25
4Kim2019-10-2509:25:242019-10-25
5Ramesh2019-10-2509:27:162019-10-25
6Suresh2019-10-2509:28:192019-10-25

❮ MySQL Functions

5