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.

This function is useful in -

• Value position: It allows you to determine the position of a value within a list.
• Sorting based on custom order: FIELD() can be used in an ORDER BY clause to sort data based on a custom order.

Syntax:

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

Arguments:

MySQL Version: 8.0

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

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

Output:

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

Basic Usage of custom sorting

The following MySQL statement will retrieve the snacks after sorted based on their popularity as mention in the list.

Code:

SELECT name
FROM snacks
ORDER BY FIELD(name, 'Cakes', 'Biscuit', 'Chocolate', 'Cookis','Chips');

Sample table: snacks

Output:

name     |
---------+
Cakes    |
Biscuit  |
Chocolate|
Cookis   |
Chips    |

Conditional Sorting using FIELD()

The following MySQL statement will sort the participants based on gender and age.

Code:

SELECT p_name, p_age, p_mf
FROM participant
ORDER BY FIELD(p_mf, 'M', 'F'), p_age;

Sample table: participant

Output:

p_name|p_age|p_mf|
------+-----+----+
Alex  |   19|M   |
Bentic|   19|M   |
John  |   19|M   |
Kerry |   21|M   |
Pitter|   23|M   |
Senai |   18|F   |
Oli   |   19|F   |
Pelin |   21|F   |

In this example, the records will be sorted first by gender (placing males before females), and then within each gender group, they will be sorted by age.

Video Presentation:

All String Functions (Slides presentation)

Previous: EXPORT_SET
Next: FIND_IN_SET