Mysql FIELD() function

FIELD() 

MySQL FIELD() returns the index position of the searching string from a list of strings. If the search string is not found, it returns a 0(zero). If the search string is NULL, the return value is 0 because NULL fails equality comparison with any value.

FIELD() is the complement of ELT(). When all arguments of the FIELD() are strings, they are compared as strings. If all arguments are a number, they compared as numbers. Otherwise all are compared as double.

Syntax:

FIELD(search string, string1, string2, string3…..) 

Arguments

Name	Description
search string	A string which is to be found in the following list of strings specified as arguments.
string1	First string to be checked if it is containing the first argument (i.e. search string).
string2	Second string to be checked if it is containing the first argument (i.e. search string).
string3	Third string to be checked if it is containing the first argument (i.e. search string). Up to N number of strings can be specified in this way.

MySQL Version: 5.6

Pictorial Presentation

Example : MySQL FIELD() function

The following MySQL statement finds the string ‘ank’ at the 2nd place within the list of the arguments. So it returns 2.

Code:

SELECT FIELD('ank', 'b', 'ank', 'of', 'monk');



Copy

Sample Output:

mysql> SELECT FIELD('ank', 'b', 'ank', 'of', 'monk');
+----------------------------------------+
| FIELD('ank', 'b', 'ank', 'of', 'monk') |
+----------------------------------------+
|                                      2 | 
+----------------------------------------+
1 row in set (0.00 sec)

MySQL FIELD() function with not in the arguments

The following MySQL statement does not finds the string ‘ank’ in the list of the arguments. So it returns 0.

Code:

SELECT FIELD('ank','b','and','of','monk'); 



Copy

Sample Output:

mysql> SELECT FIELD('ank','b','and','of','monk');
+------------------------------------+
| FIELD('ank','b','and','of','monk') |
+------------------------------------+
|                                  0 | 
+------------------------------------+
1 row in set (0.00 sec)