MySQL ELT() Function

The MySQL ELT() function is used to retrieve a specific string value from a set of strings based on an index number.

It accepts a numerical value (say I) and a list of strings as parameters and returns the Ith element of the given list. The numerical value I should be less than the number of arguments passed, and should be greater than 0 or else, this function returns NULL.

Syntax

Following is the syntax of MySQL ELT() function −

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

Parameters

This function takes an index and a list of string values as parameter.

Return Value

This function returns the string at the specified index from the given list.

Example

In this example, we are using the ELT() function to retrieve the 3rd element from a list of strings −

SELECT ELT( 3, 'Java', 'JavaFX', 'OpenCV', 'WebGL');

Following is the output of the above code −

ELT( 3, 'Java', 'JavaFX', 'OpenCV', 'WebGL')
OpenCV

Example

In here, we are providing the index as the numerical value '4' (in string form) and retrieving the corresponding element from a list of strings −

SELECT ELT( '4', 'test1', 'test2', 'test3', 'test4');

Output of the above code is as shown below −

ELT( '4', 'test1', 'test2', 'test3', 'test4')
test4

Example

If you pass a numerical value less than or equal to 0 as the first argument to the ELT() function, it returns NULL −

SELECT ELT( -9, 'Java', 'JavaFX', 'OpenCV', 'WebGL');

The output obtained is as shown below −

ELT( -9, 'Java', 'JavaFX', 'OpenCV', 'WebGL')
NULL

Example

When you pass a numerical value greater than the number of arguments passed to the ELT() function, it returns NULL −

SELECT ELT( 9, 'Java', 'JavaFX', 'OpenCV', 'WebGL', NULL);

The result produced is as follows −

ELT( 9, 'Java', 'JavaFX', 'OpenCV', 'WebGL', NULL)
NULL

Example

If you pass NULL as the first argument, the function returns NULL −

SELECT ELT( NULL, 'Apple', 'Orange', 'Mango', 'Banana');

We get the output as follows −

ELT( NULL, 'Apple', 'Orange', 'Mango', 'Banana')
NULL

Example

The list of strings given to this function may also contain NULL values or empty strings −

SELECT ELT( 3, 'Java', 'JavaFX', 'OpenCV', 'WebGL', NULL);

The output obtained is as shown below −

ELT( 3, 'Java', 'JavaFX', 'OpenCV', 'WebGL', NULL)
OpenCV

Example

Let us create a table named "FILM_RATINGS" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE FILM_RATINGS(
   MOVIE varchar(50),
   RATING int
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO FILM_RATINGS VALUES 
('RRR', 5),
('Pushpa', 4),
('Bahubali', 5),
('Tubelight', 1),
('Sitaramam', 3),
('Bharat', 2);

The FILM_RATINGS table obtained is as follows −

MOVIE RATING
RRR 5
Pushpa 4
Bahubali 5
Tubelight 1
Sitaramam 3
Bharat 2

Now, let us add a column named OVERALL_REVIEW to the FILM_RATINGS table using the following query −

ALTER TABLE FILM_RATINGS ADD COLUMN OVERALL_REVIEW VARCHAR(40);

The following query updates the "OVERALL_REVIEW" column in the "FILM_RATINGS" table based on the values of "RATING" column using the MySQL ELT() function −

UPDATE FILM_RATINGS 
SET OVERALL_REVIEW = ELT(RATING, 'Disaster', 'Flop', 'Hit', 'Blockbuster', 'Industry Hit');

Following is the resultant FILM_RATINGS table −

MOVIE RATING OVERALL_REVIEW
RRR 5 Industry Hit
Pushpa 4 Blockbuster
Bahubali 5 Industry Hit
Tubelight 1 Disaster
Sitaramam 3 Hit
Bharat 2 Flop
mysql-elt-function.htm
Advertisements