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 |