w3resource logo
MYSQL Tutorial

MySQL FIELD() function

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

MySQL FIELD() 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

MySQL String Functions, slide presentation