How the FIELD() function works in Mariadb?

The MariaDB FIELD() function is used to return the index position of a string within a list of strings. It’s a useful function when you need to sort data based on a custom list or to find the position of a string in a set of values.

Syntax

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

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

• str is the string to find within the list.
• str1, str2, str3, … are the strings in the list to search through.

Examples

Example 1: Basic Usage of FIELD()

To find the position of a string in a list:

SELECT FIELD('b', 'a', 'b', 'c') AS position;

The output will be:

+----------+
| position |
+----------+
|        2 |
+----------+

This indicates that ‘b’ is the second item in the list.

Example 2: FIELD() with Non-Matching String

To see what happens when the string is not found:

SELECT FIELD('d', 'a', 'b', 'c') AS position;

The output will be:

+----------+
| position |
+----------+
|        0 |
+----------+

Since ’d’ is not in the list, the function returns 0.

Example 3: FIELD() with Numeric Values

Using FIELD() to find the position of a number in a list of numbers:

SELECT FIELD(3, 1, 2, 3, 4) AS position;

The output will be:

+----------+
| position |
+----------+
|        3 |
+----------+

The number 3 is the third item in the list.

Example 4: FIELD() with Table Data

Using FIELD() to find the position of a string from a column:

DROP TABLE IF EXISTS colors;
CREATE TABLE colors (
    color_name VARCHAR(10)
);
INSERT INTO colors VALUES ('red'), ('green'), ('blue');

SELECT color_name, FIELD(color_name, 'green', 'red', 'blue') AS position FROM colors;

The output will show the position of each color based on the custom list:

+------------+----------+
| color_name | position |
+------------+----------+
| red        |        2 |
| green      |        1 |
| blue       |        3 |
+------------+----------+

Related Functions

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

• MariaDB ELT() function returns the N-th element from a list of strings.
• MariaDB FIND_IN_SET() function returns the position of a string within a list of comma-separated strings.
• MariaDB INSTR() function returns the position of the first occurrence of a substring in a string.

Conclusion

The FIELD() function in MariaDB is a straightforward and efficient way to determine the index of a given string within a list of strings. It is particularly useful for custom sorting operations and for checking the presence and position of elements in a list. By mastering FIELD() and its related functions, developers can handle string lists more effectively in their database operations.