SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

ELT() vs FILED() Functions

on March 28, 2016

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.

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.

1
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

1
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.

1
SELECT ELT(31,'SQL','ORACLE','My SQL','SQL SERVER') AS ColumnName;
1
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

1
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

1
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: