FIELD()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Overview
Returns the position of the first argument's string in the subsequent string arguments.
USAGE
FIELD(find, value[, value] ...)
Argument Name | Description |
|---|---|
| The value that is being searched for |
| One or more values to search through |
DETAILS
FIELD() is a string function that returns the index of the find argument from a list of arguments.
The return value is the 1-based index position of the first match in the list or 0 if it is not found.
The arguments must be strings, numbers, or doubles.
If the items being compared do not match in type, an inexact comparison is performed. For instance, the string 5-fold will match the integer 5 regardless of which one is the find term and which one is the list term, but if both arguments are strings they do not match.
Searching for NULL always returns 0.
EXAMPLES
SELECT FIELD('sad', 'good', 'bad', 'neutral');
+----------------------------------------+
| FIELD('sad', 'good', 'bad', 'neutral') |
+----------------------------------------+
| 0 |
+----------------------------------------+
SELECT FIELD('bad', 'good', 'bad', 'neutral');
+----------------------------------------+
| FIELD('bad', 'good', 'bad', 'neutral') |
+----------------------------------------+
| 2 |
+----------------------------------------+
-- Disable strict mode or the select might throw an error
SET sql_mode = '';
SELECT FIELD('5-fold', 'good', 5, 'bad', '5-fold');
+---------------------------------------------+
| FIELD('5-fold', 'good', 5, 'bad', '5-fold') |
+---------------------------------------------+
| 2 |
+---------------------------------------------+
Precision
Precision is a factor when comparing function arguments. As illustrated by the following example, a double value is accurate up to 15 decimal places.
SELECT FIELD(1, 1.000000000000001, 1);
+--------------------------------+
| FIELD(1, 1.000000000000001, 1) |
+--------------------------------+
| 2 |
+--------------------------------+
SELECT FIELD(1, 1.0000000000000001, 1);
+---------------------------------+
| FIELD(1, 1.0000000000000001, 1) |
+---------------------------------+
| 1 |
+---------------------------------+