MySQL – ELT() and FILED() Functions to Extract Index Position From List

MySQL supports some functions that can be used to extract the index position or the actual value from a set of values arranged as an array. ELT() and FIELD() are example of such functions. Let us understand about them in details. 

ELT() function accepts many arguments where the first argument is the number. It returns the value which is available at the position specified in the first argument.

SELECT ELT(3,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

The above query returns results as “My SQL”. From the set of values available, it returns the 3rd value which is My SQL. It returns NULL if the first argument is 0, less than zero, or greater than the number of total arguments

SELECT ELT(0,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

Here are another two examples which will return the result as a null value.

SELECT ELT(31,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

SELECT ELT(-5,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

All the above return NULL value.

FILED() function is just opposite of ELT(). It accepts many arguments and returns the position of the value in the array specified as the first value

SELECT FIELD('My SQL','SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

The above returns 3 as the value specified in the first argument “My SQL” is available at the 3rd position in the array.

It returns 0 if the value is not specified in the array

SELECT FIELD('COMPUTER','SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;

The result is 0.

Have you ever used these functions in your business logic? If yes, I would like to know them.

Reference: Pinal Dave (https://blog.sqlauthority.com)