w3resource

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:

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: 8.0

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

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/string-functions/mysql-field-function.php