In MariaDB, FIELD() is a built-in string function that returns the index position of the string or number matching the specified pattern.

It accepts two or more arguments. The first is the pattern you want to search for. All subsequent arguments are the strings or numbers for which to match the pattern against.

Syntax

The syntax goes like this:

FIELD(pattern, str1[,str2,...])

Where pattern is the pattern, and str1[, str2,…] represents the strings or numbers.

Example

Here’s a basic example:

SELECT FIELD('Green', 'Red', 'Green', 'Blue');

Result:

+----------------------------------------+
| FIELD('Green', 'Red', 'Green', 'Blue') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+

In this case, the pattern (Green) occurs at the second position, and so 2 is returned.

Case Sensitivity

The FIELD() function treats strings as case-insensitive.

Example:

SELECT FIELD('BLUE', 'Red', 'Green', 'Blue');

Result:

+---------------------------------------+
| FIELD('BLUE', 'Red', 'Green', 'Blue') |
+---------------------------------------+
|                                     3 |
+---------------------------------------+

No Match

If there’s no match, 0 is returned.

Example:

SELECT FIELD('Go', 'Red', 'Green', 'Blue');

Result:

+-------------------------------------+
| FIELD('Go', 'Red', 'Green', 'Blue') |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

Numbers

When all arguments are numbers, they are treated as numbers. Otherwise they’re treated as doubles.

Example:

SELECT FIELD(3, 40, 2, 18, 10, 3);

Result:

+----------------------------+
| FIELD(3, 40, 2, 18, 10, 3) |
+----------------------------+
|                          5 |
+----------------------------+

Multiple Matches

If there are multiple matches of the pattern, only the index of the first one is returned:

SELECT FIELD(3, 40, 3, 18, 10, 3);

Result:

+----------------------------+
| FIELD(3, 40, 3, 18, 10, 3) |
+----------------------------+
|                          2 |
+----------------------------+

Specifying a Null Pattern

Providing null as the first argument results in 0:

SELECT FIELD(null, 1, 2, 3);

Result:

+----------------------+
| FIELD(null, 1, 2, 3) |
+----------------------+
|                    0 |
+----------------------+

Providing Just One String Argument

Providing a single argument to match is valid:

SELECT FIELD(205, 205);

Result:

+-----------------+
| FIELD(205, 205) |
+-----------------+
|               1 |
+-----------------+

Single Argument

But passing just one argument to FIELD() returns an error:

SELECT FIELD(205);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FIELD'

Missing Argument

Calling FIELD() without passing any arguments results in an error:

SELECT FIELD();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FIELD'