MySQL FIELD() function

Last update on April 04 2022 13:11:42 (UTC/GMT +8 hours)

FIELD() function

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

Video Presentation:

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');

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');

Sample Output:

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

All String Functions

Previous: EXPORT_SET
Next: FIND_IN_SET