FIELD()

Description

FIELD() returns the index (position) of str in the str1, str2, str3, … list.

Syntax

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

Arguments

ArgumentsDescription
strRequired. The value to search for in the list, case insensitive.
str1,str2,str3,…Required. A list of values to search for, case insensitive.

Returned Value

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

  • The FIELD() function returns the corresponding position index if the specified value is found in the list. Indexes returned by the FIELD() function start at 1.

  • If more than one specified value is found in the list, the FIELD() function returns only the index of the first one.

  • Returns 0 if str is not found.

  • If str is NULL, the return value is 0 because NULL fails equality comparison with any value.

Examples

  • Example 1:
  1. mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
  2. +-------------------------------+
  3. | field(Bb, Aa, Bb, Cc, Dd, Ff) |
  4. +-------------------------------+
  5. |                             2 |
  6. +-------------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
  10. +-------------------------------+
  11. | field(Gg, Aa, Bb, Cc, Dd, Ff) |
  12. +-------------------------------+
  13. |                             0 |
  14. +-------------------------------+
  15. 1 row in set (0.00 sec)
  • Example 2:
  1. drop table if exists t;
  2. create table t(
  3.     i int,
  4.     f float,
  5.     d double
  6. );
  7. insert into t() values (1, 1.1, 2.2), (2, 3.3, 4.4), (0, 0, 0), (0, null, 0);
  9. mysql> select * from t;
  10. +------+------+------+
  11. | i    | f    | d    |
  12. +------+------+------+
  13. |    1 |  1.1 |  2.2 |
  14. |    2 |  3.3 |  4.4 |
  15. |    0 |    0 |    0 |
  16. |    0 | NULL |    0 |
  17. +------+------+------+
  18. 4 rows in set (0.01 sec)
  20. mysql> select field(1, i, f, d) from t;
  21. +-------------------+
  22. | field(1, i, f, d) |
  23. +-------------------+
  24. |                 1 |
  25. |                 0 |
  26. |                 0 |
  27. |                 0 |
  28. +-------------------+
  29. 4 rows in set (0.01 sec)
  31. mysql> select field(i, f, d, 0, 1, 2) from t;
  32. +-------------------------+
  33. | field(i, f, d, 0, 1, 2) |
  34. +-------------------------+
  35. |                       4 |
  36. |                       5 |
  37. |                       1 |
  38. |                       2 |
  39. +-------------------------+
  40. 4 rows in set (0.01 sec)
  42. mysql> select field('1', f, d, 0, 1, 2) from t;
  43. +-------------------------+
  44. | field(1, f, d, 0, 1, 2) |
  45. +-------------------------+
  46. |                       4 |
  47. |                       4 |
  48. |                       4 |
  49. |                       4 |
  50. +-------------------------+
  51. 4 rows in set (0.01 sec)