MySQL | FIELD method
Start your free 7-days trial now!
MySQL's FIELD(~) method returns the position of a string in a list of strings.
Parameters
1. str | string / number
The string to find in the list of provided strings. Can also be a number.
2. str1 | string / number
A list of strings. Can also be numbers, however, there must be consistency in data type with str.
Return value
Case | Return value |
|---|---|
Match is found | Position of first match |
Match is NOT found | 0 |
If | 0 |
Examples
Strings
To find the position of 'shark' in the provided list of strings:
SELECT FIELD('shark', 'Park', 'Shark', 'Dark', 'Pear');
+---------------------------------------------+| FIELD('shark','Park','Shark','Dark','Pear') |+---------------------------------------------+| 2 |+---------------------------------------------+
Note that the match is case insensitive as we find a match with the second item in the list.
The match cannot be a partial match:
SELECT FIELD('ark', 'Park', 'Shark', 'Dark', 'Pear');
+-----------------------------------------------+| FIELD('ark', 'Park', 'Shark', 'Dark', 'Pear') |+-----------------------------------------------+| 0 |+-----------------------------------------------+
Note that 'ark' does not fully match with any of 'Park', 'Shark', 'Dark' or 'Pear' hence the 0 return value.
Numbers
To find the position of 123 in a list of numbers:
SELECT FIELD(123, 456, 789, 123);
+---------------------------+| FIELD(123, 456, 789, 123) |+---------------------------+| 3 |+---------------------------+
Remember that the first item in the list is 456 and not 123.
