How the ELT() function works in Mariadb?

The ELT() function is a string function that returns the element at a specified index from a list of strings.

Posted on

The ELT() function is a string function that returns the element at a specified index from a list of strings. It is useful for mapping numeric values to corresponding strings, such as month names, weekday names, or status codes.

Syntax

The syntax of the ELT() function is as follows:

ELT(index, string1, string2, ..., stringN)

The function takes one or more arguments, where:

  • index is a positive integer that specifies the position of the element to return from the list of strings. If index is 0 or negative, the function returns an empty string. If index is greater than the number of strings, the function returns NULL.
  • string1, string2, …, stringN are the strings that form the list of elements. There can be up to 254 strings in the list.

Examples

Example 1: Returning the month name from a date

In this example, we use the ELT() function to return the month name from a date column. We also use the MONTH() function to extract the month number from the date.

SELECT date, ELT(MONTH(date), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') AS month_name
FROM orders;

The output is:

+------------+------------+
| date       | month_name |
+------------+------------+
| 2023-01-15 | January    |
| 2023-02-18 | February   |
| 2023-03-12 | March      |
| 2023-04-21 | April      |
| 2023-05-05 | May        |
| 2023-06-30 | June       |
| 2023-07-14 | July       |
| 2023-08-25 | August     |
| 2023-09-09 | September  |
| 2023-10-31 | October    |
| 2023-11-11 | November   |
| 2023-12-24 | December   |
+------------+------------+

Example 2: Returning the weekday name from a date

In this example, we use the ELT() function to return the weekday name from a date column. We also use the WEEKDAY() function to extract the weekday number from the date. Note that the WEEKDAY() function returns 0 for Monday, 1 for Tuesday, and so on, so we need to add 1 to the result to match the ELT() function.

SELECT date, ELT(WEEKDAY(date) + 1, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') AS weekday_name
FROM orders;

The output is:

+------------+-------------+
| date       | weekday_name|
+------------+-------------+
| 2023-01-15 | Sunday      |
| 2023-02-18 | Friday      |
| 2023-03-12 | Saturday    |
| 2023-04-21 | Thursday    |
| 2023-05-05 | Thursday    |
| 2023-06-30 | Thursday    |
| 2023-07-14 | Thursday    |
| 2023-08-25 | Thursday    |
| 2023-09-09 | Friday      |
| 2023-10-31 | Monday      |
| 2023-11-11 | Friday      |
| 2023-12-24 | Saturday    |
+------------+-------------+

Example 3: Returning the status name from a code

In this example, we use the ELT() function to return the status name from a code column. We assume that the code column has values from 1 to 4, representing different statuses.

SELECT code, ELT(code, 'Pending', 'Approved', 'Rejected', 'Cancelled') AS status_name
FROM applications;

The output is:

+------+-------------+
| code | status_name |
+------+-------------+
| 1    | Pending     |
| 2    | Approved    |
| 3    | Rejected    |
| 4    | Cancelled   |
| 2    | Approved    |
| 1    | Pending     |
| 3    | Rejected    |
| 4    | Cancelled   |
| 2    | Approved    |
| 1    | Pending     |
+------+-------------+

Some of the functions that are related to the ELT() function are:

  • FIELD() function: This function performs the opposite operation of the ELT() function. It returns the index of a string in a list of strings. For example, FIELD('March', 'January', 'February', 'March', 'April') returns 3.
  • CONCAT() function: This function concatenates two or more strings into one string. For example, CONCAT('Hello', ' ', 'World') returns ‘Hello World’.
  • SUBSTRING() function: This function returns a substring from a string. For example, SUBSTRING('Hello World', 1, 5) returns ‘Hello’.

Conclusion

The ELT() function is a useful string function that can map numeric values to corresponding strings. It can be used to return month names, weekday names, status names, or any other custom strings from a list of strings. It takes an index and a list of strings as arguments, and returns the element at the specified index from the list. If the index is invalid, it returns an empty string or NULL. The ELT() function can be combined with other functions, such as MONTH(), WEEKDAY(), or FIELD(), to perform various string operations.