w3resource
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

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.