How the ELT() function works in Mariadb?

The MariaDB ELT() function is used to return the N-th element from a list of strings. It is essentially the opposite of the FIELD() function, providing a simple way to access elements based on their numeric position.

Syntax

The syntax for the MariaDB ELT() function is as follows:

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

• N is an integer indicating the position of the element to return.
• str1, str2, str3, … are the strings from which to return the N-th element.

Examples

Example 1: Returning the First Element

To return the first element from a list of strings:

SELECT ELT(1, 'apple', 'banana', 'cherry') AS fruit;

The output will be:

+-------+
| fruit |
+-------+
| apple |
+-------+

Example 2: Returning the Third Element

To return the third element from a list of strings:

SELECT ELT(3, 'red', 'green', 'blue') AS color;

The output will be:

+-------+
| color |
+-------+
| blue  |
+-------+

Example 3: Returning an Element with a Variable Position

To return an element from a variable position:

SET @position = 2;
SELECT ELT(@position, 'first', 'second', 'third') AS ordinal;

The output will be:

+---------+
| ordinal |
+---------+
| second  |
+---------+

Example 4: Returning an Element Beyond the List Range

To attempt to return an element beyond the range of the list:

SELECT ELT(5, 'one', 'two', 'three') AS number;

The output will be NULL because there is no fifth element:

+--------+
| number |
+--------+
| NULL   |
+--------+

Example 5: Using ELT() with a Table Column

To use ELT() to return elements based on a table column:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT,
    product_id INT
);
INSERT INTO orders VALUES (1, 3), (2, 1);

SELECT id, ELT(product_id, 'TV', 'Laptop', 'Camera') AS product FROM orders;

The output will show the product names based on the product_id:

+------+---------+
| id   | product |
+------+---------+
|    1 | Camera  |
|    2 | TV      |
+------+---------+

Related Functions

Here are a few functions related to the MariaDB ELT() function:

• MariaDB FIELD() function returns the index position of a string within a list of strings.
• MariaDB CONCAT_WS() function concatenates strings with a separator.
• MariaDB FIND_IN_SET() function returns the position of a string within a list of comma-separated strings.

Conclusion

The ELT() function in MariaDB is a convenient tool for retrieving specific elements from a list based on their numeric index. It can be particularly useful in scenarios where the position of data is known and consistent, such as when working with enum-like data sets or fixed arrays. By understanding how to use ELT() effectively, developers can simplify data retrieval tasks and improve the readability of their SQL queries.