How the FIELD() function works in Mariadb?

The FIELD() function is a string function that returns the index position of a value in a list of values.

Posted on

The FIELD() function is a string function that returns the index position of a value in a list of values. It can be used to compare and sort values in a table or a result set.

Syntax

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

FIELD(str, str1, str2, ..., strN)

The function takes one or more arguments:

  • str is the value to be searched in the list of values.
  • str1, str2, …, strN are the values in the list to be compared with str.

The function returns an integer value that indicates the position of str in the list of values. If str is not found in the list, the function returns 0. If str is NULL, the function returns NULL.

Examples

Example 1: Using FIELD() function with literal values

The following example shows how to use the FIELD() function with literal values:

SELECT FIELD('B', 'A', 'B', 'C', 'D');

The function returns 2, because ‘B’ is the second value in the list.

+-----------------------------+
| FIELD('B', 'A', 'B', 'C', 'D') |
+-----------------------------+
|                           2 |
+-----------------------------+

Example 2: Using FIELD() function with column values

The following example shows how to use the FIELD() function with column values from a table. Suppose we have a table called products that has the following data:

+----+------------+-------+
| id | name       | price |
+----+------------+-------+
|  1 | Apple      |  2.50 |
|  2 | Banana     |  1.00 |
|  3 | Cherry     |  3.00 |
|  4 | Durian     |  5.00 |
|  5 | Elderberry |  4.00 |
+----+------------+-------+

We can use the FIELD() function to sort the products by their name in a custom order, such as ‘Durian’, ‘Cherry’, ‘Banana’, ‘Elderberry’, ‘Apple’:

SELECT id, name, price
FROM products
ORDER BY FIELD(name, 'Durian', 'Cherry', 'Banana', 'Elderberry', 'Apple');

The function returns the position of each product name in the list, and the ORDER BY clause sorts the products by the ascending order of the position values.

+----+------------+-------+
| id | name       | price |
+----+------------+-------+
|  4 | Durian     |  5.00 |
|  3 | Cherry     |  3.00 |
|  2 | Banana     |  1.00 |
|  5 | Elderberry |  4.00 |
|  1 | Apple      |  2.50 |
+----+------------+-------+

Example 3: Using FIELD() function with expressions

The following example shows how to use the FIELD() function with expressions. Suppose we have a table called employees that has the following data:

+----+-------+--------+------------+
| id | name  | salary | hire_date  |
+----+-------+--------+------------+
|  1 | Alice |  5000  | 2020-01-01 |
|  2 | Bob   |  6000  | 2020-02-01 |
|  3 | Carol |  7000  | 2020-03-01 |
|  4 | David |  8000  | 2020-04-01 |
|  5 | Eve   |  9000  | 2020-05-01 |
+----+-------+--------+------------+

We can use the FIELD() function to filter the employees who were hired in the first quarter of 2020, by using the QUARTER() function to extract the quarter from the hire_date column:

SELECT id, name, salary, hire_date
FROM employees
WHERE FIELD(QUARTER(hire_date), 1, 2, 3) > 0;

The function returns the position of the quarter value in the list, and the WHERE clause filters the employees who have a positive position value, which means they were hired in the first, second, or third quarter.

+----+-------+--------+------------+
| id | name  | salary | hire_date  |
+----+-------+--------+------------+
|  1 | Alice |  5000  | 2020-01-01 |
|  2 | Bob   |  6000  | 2020-02-01 |
|  3 | Carol |  7000  | 2020-03-01 |
+----+-------+--------+------------+

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

  • FIND_IN_SET() function: This function returns the position of a string in a comma-separated list of strings. For example, FIND_IN_SET('B', 'A,B,C,D') returns 2.
  • ELT() function: This function returns the element at a specified position in a list of values. It is the inverse of the FIELD() function. For example, ELT(2, 'A', 'B', 'C', 'D') returns ‘B’.
  • IN() operator: This operator returns 1 if a value is in a list of values, or 0 otherwise. For example, 'B' IN ('A', 'B', 'C', 'D') returns 1.

Conclusion

The FIELD() function is a useful string function that can be used to compare and sort values in a list. It returns the position of a value in a list of values, or 0 if the value is not found. It can be used with literal values, column values, or expressions. It can also be combined with other functions or operators to perform complex queries.