# **FIELD()**

## **Function Description**

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

## **Grammar Description**

```text
> 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:

```text
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:

```text
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)
```