MySQL ELT() function

ELT() function

MySQL ELT() returns the string at the index number specified in the list of arguments. The first argument indicates the index of the string to be retrieved from the list of arguments.

It returns NULL when the index number is less than 1 or index number is greater than the number of the string specified as arguments.

Note: According to Wikipedia ELT stands for Extract, Load, Transform (ELT), a data manipulation process.

This function is useful in -

• String selection: Based on the index provided, you can select a specific string from a list of options.
• Case expression: ELT() can be used in conjunction with a CASE statement to perform conditional string selection.

Syntax:

ELT(index number, string1, string2, string3,…)

Arguments:

MySQL Version: 8.0

Pictorial Presentation

Example: MySQL ELT() function

The following MySQL statement will return the string at 4th position from the list of strings.

Code:

SELECT ELT(4,'this','is','the','elt'); 

Output:

mysql> SELECT ELT(4,'this','is','the','elt'); 
+--------------------------------+
| ELT(4,'this','is','the','elt') |
+--------------------------------+
| elt                            | 
+--------------------------------+
1 row in set (0.02 sec)

Example of MySQL ELT() function with greater index number

The following MySQL statement will return NULL because the index number is more than the number of strings.

Code:

SELECT ELT(5,'this','is','the','elt'); 

Output:

mysql> SELECT ELT(5,'this','is','the','elt');
+--------------------------------+
| ELT(5,'this','is','the','elt') |
+--------------------------------+
| NULL                           | 
+--------------------------------+
1 row in set (0.00 sec)

Example of MySQL ELT() function with index value zero(0)

The following MySQL statement will return the NULL because the index number is less than 1.

Code:

SELECT ELT(0,'this','is','the','elt');

Output:

mysql> SELECT ELT(0,'this','is','the','elt');
+--------------------------------+
| ELT(0,'this','is','the','elt') |
+--------------------------------+
| NULL                           | 
+--------------------------------+
1 row in set (0.00 sec)

Using Column Values and ELT()

The following MySQL statement will return the item_name and their category by using the ELT() based on its index.

Code:

SELECT item_name,ELT(category_id, 'Dessert','Snacks','First Food','Soups') AS category_name
FROM items_test;

Sample table: items_test

Output:

item_name   |category_name|
------------+-------------+
Chocolate   |Snacks       |
Biscuit     |Snacks       |
Cake        |Snacks       |
Icecream    |Dessert      |
Momo        |First Food   |
Cold-drinks |Dessert      |
Checken soup|Soups        |
Pizza       |First Food   |

ELT() with NULL Values

Code:

SELECT ELT(3, 'One', NULL, 'Three', 'Four') AS Result;

Output:

Result|
------+
Three |

In this example, the ELT() function skips the NULL value at index 2 and returns the string at index 3.

Using ELT() with a Subquery

Code:

SELECT ELT((SELECT category_id FROM items_test WHERE item_code = 4), 'Dessert','Snacks','First Food','Soups') AS category_name;

Sample table: items_test

Output:

category_name|
-------------+
Dessert      |

In this example, the ELT() function uses the result of a subquery as the index to retrieve the corresponding category name.

Using ELT() in a Comparison

Code:

SELECT item_code, item_name
FROM items_test
WHERE ELT(category_id, 'Dessert','Snacks','First Food','Soups') = 'Snacks';

Sample table: items_test

Output:

item_code|item_name|
---------+---------+
        1|Chocolate|
        2|Biscuit  |
        3|Cake     |

In this query, the ELT() function is used in the WHERE clause to filter products with the category 'Snacks'.

Video Presentation:

All String Functions (Slides presentation)

Previous: CONCAT
Next: EXPORT_SET