MySQL FIELD() and ELT()

By /
FIELD And ELT Functions

In this tutorial, we will learn about the MySQL FIELD and ELT functions.

  • The FIELD() function is used to return the position of value from a list of values. FIELD() is a case-insensitive function. 
  • The ELT() function does the exact opposite of the FIELD() function. It is used to return the value at a given position from a list of values. 

Both the functions are widely used with the SELECT statement.

Syntax of FIELD() function

FIELD(value, val1, val2, val3, … );Code language: SQL (Structured Query Language) (sql)

Where,

  • value is the value whose position in the list has to be returned,
  • val1, val2, val3, … is the list in which the ‘value’ has to be searched.

Syntax of ELT() function

ELT(index number, val1, val2, val3, … );Code language: SQL (Structured Query Language) (sql)

Where,

  • index number is the position number of the value in the list,
  • val1, val2, val3, … is the list in which the value at the given ‘index number’ has to be searched.

Examples of MySQL FIELD()

Let us take a look at the basic examples of MySQL FIELD(). Consider the below queries. We use aliases to make our output readable.

SELECT FIELD('w', 'e', 'r', 'w', 't') AS Position; 
SELECT FIELD(15, 12, 15, 20, 36, 55) AS Position; Code language: SQL (Structured Query Language) (sql)

In the first query, we are finding the position of ‘w’ in the list – ‘e’, ‘r’, ‘w’, ‘t’. In the second query, we are aiming to find the position of the number 15 in a list of numbers containing 12, 15, 20, 36, 55.

We get our output as,

MySQL Field Basic Example

MySQL FIELD() When Value To Be Searched Is Not In The List

Let us find the position of 34 in a list of numbers containing the following values – 12, 15, 20, 36, 55. The query for it is,

SELECT FIELD(34, 12, 15, 20, 36, 55) AS Position;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Field Not Found

Since the value is not present, FIELD() returns 0. It is important to note that FIELD() returns 0 if the given value is not found in the list.

MySQL FIELD() When List Contains NULL

Suppose you have the following list – 12, 15, NULL, 36, 55. Let us try to find the position of 3 in the list. Our query is,

SELECT FIELD(3, 12, 15, NULL, 36, 55) AS Position;Code language: SQL (Structured Query Language) (sql)

And our output is,

Field Null Value

Wait, 3 is not in the list so technically our output should have been 0. Why is it NULL? This is because the list has a NULL value. FIELD() returns NULL if any value in the list is NULL.

Examples of MySQL ELT()

Let us take a look at the basic examples of MySQL ELT(). Consider the below queries. We use aliases to make our output readable.

SELECT ELT(1, 'e', 'r', 'w', 't') AS CharacterAtPosition; 
SELECT ELT(5, 12, 15, 20, 36, 55) AS CharacterAtPosition;Code language: SQL (Structured Query Language) (sql)

In the first query, we are finding the value at position 1 in the list – ‘e’, ‘r’, ‘w’, ‘t’. In the second query, we are finding the value at position 5 in the list – 12, 15, 20, 36, 55.

And our output is,

Elt Basic Example

MySQL ELT() When Index Number Is Not Present

What if your list has 10 values but you say you want to know the value at index 15? Or what if you want to know the value at index 0 in a list? Let us take a look at such examples. Consider the below queries.

SELECT ELT(15, 12, 15, 20, 36, 55) AS CharacterAtPosition; 
SELECT ELT(0, 12, 15, 20, 36, 55) AS CharacterAtPosition;Code language: SQL (Structured Query Language) (sql)

In the first query, we want to find the value at position 15. As you can see, our list only has 5 values in it. In the second query, we are trying to find the value at position 0 in the same list. The output for the above queries is,

Elt Not Found

As you can see, both queries return NULL. ELT() returns NULL if the index position is 0 or exceeds the length of the list.

MySQL ELT() When List Contains NULL Value

ELT() remains unaffected even if there is a NULL value in the list. This can be demonstrated using the two queries below –

SELECT ELT(5, 12, 15, 20, 36, NULL) AS CharacterAtPosition; 
SELECT ELT(2, 12, 15, 20, 36, NULL) AS CharacterAtPosition;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Elt Null Value

As you can see, in both cases, the right value at the given position was returned by ELT() even if it was a NULL value.

Conclusion

FIELD() and ELT() are very useful while exploring values in a list. I would recommend you to check out the references for further reading.

References