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.
str1,str2,str3,…Required. A list of values to search for.

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.

  • 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)
  8. mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
  9. +-------------------------------+
  10. | field(Gg, Aa, Bb, Cc, Dd, Ff) |
  11. +-------------------------------+
  12. | 0 |
  13. +-------------------------------+
  14. 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);
  8. mysql> select * from t;
  9. +------+------+------+
  10. | i | f | d |
  11. +------+------+------+
  12. | 1 | 1.1 | 2.2 |
  13. | 2 | 3.3 | 4.4 |
  14. | 0 | 0 | 0 |
  15. | 0 | NULL | 0 |
  16. +------+------+------+
  17. 4 rows in set (0.01 sec)
  18. mysql> select field(1, i, f, d) from t;
  19. +-------------------+
  20. | field(1, i, f, d) |
  21. +-------------------+
  22. | 1 |
  23. | 0 |
  24. | 0 |
  25. | 0 |
  26. +-------------------+
  27. 4 rows in set (0.01 sec)
  28. mysql> select field(i, f, d, 0, 1, 2) from t;
  29. +-------------------------+
  30. | field(i, f, d, 0, 1, 2) |
  31. +-------------------------+
  32. | 4 |
  33. | 5 |
  34. | 1 |
  35. | 2 |
  36. +-------------------------+
  37. 4 rows in set (0.01 sec)
  38. mysql> select field('1', f, d, 0, 1, 2) from t;
  39. +-------------------------+
  40. | field(1, f, d, 0, 1, 2) |
  41. +-------------------------+
  42. | 4 |
  43. | 4 |
  44. | 4 |
  45. | 4 |
  46. +-------------------------+
  47. 4 rows in set (0.01 sec)