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;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position1

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;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position2
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;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position3

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;

MySQL - ELT() and FILED() Functions to Extract Index Position From List mysql-index-position4

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)

, ,
Previous Post
“I Don’t Know” – DBAs Should Learn to Use This Phrase – Notes from the Field #119
Next Post
SQL SERVER – Patch Upgrade – Failed to connect to server. Error: 0x80070422

Related Posts

Leave a Reply

Menu