Skip to content

FIELD()

Function Description

The FIELD() function returns the position of the first string str in the string list (str1,str2,str3,...).

Grammar Description

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

Parameter definition

Parameters Description
str Required parameters. The value to be found in the list is case-insensitive.
str1,str2,str3,... Required parameters. Each element in the searched list is case-insensitive.

Return value

If all parameters of FIELD() are of type string, all parameters are compared as string. If all parameters are numbers, compare them as numbers. If all parameters are of double type, they are compared as double type.

  • If the specified value is found in the list, the FIELD() function returns the corresponding position index. The value of the index returned by the FIELD() function starts from 1.

  • If multiple specified values ​​are found in the list, the FIELD() function returns only the first index value.

  • If the specified value cannot be found in the list, the FIELD() function returns 0.

  • If the value you are looking for is NULL, the FIELD() function returns 0.

Example

  • Example 1:
mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
+------------------------------------+
| field(Bb, Aa, Bb, Cc, Dd, Ff) |
+------------------------------------+
| 2 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
+------------------------------------+
| field(Gg, Aa, Bb, Cc, Dd, Ff) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
  • Example 2:
drop table if exists t;
create table t(
    i int,
    f float,
    d double
);
insert into t() values ​​(1, 1.1, 2.2), (2, 3.3, 4.4), (0, 0, 0), (0, null, 0);

mysql> select * from t;
+------+------+
| i | f | d |
+------+------+
| 1 | 1.1 | 2.2 |
| 2 | 3.3 | 4.4 |
| 0 | 0 | 0 |
| 0 | NULL | 0 |
+------+------+
4 rows in set (0.01 sec)

mysql> select field(1, i, f, d) from t;
+-------------------------+
| field(1, i, f, d) |
+-------------------------+
| 1 |
| 0 |
| 0 |
| 0 |
+-------------------------+
4 rows in set (0.01 sec)

mysql> select field(i, f, d, 0, 1, 2) from t;
+------------------------------+
| field(i, f, d, 0, 1, 2) |
+------------------------------+
| 4 |
| 5 |
| 1 |
| 2 |
+------------------------------+
4 rows in set (0.01 sec)

mysql> select field('1', f, d, 0, 1, 2) from t;
+------------------------------+
| field(1, f, d, 0, 1, 2) |
+------------------------------+
| 4 |
| 4 |
| 4 |
| 4 |
+------------------------------+
4 rows in set (0.01 sec)