MySQL FIELD() Function

---

---

The MySQL FIELD() function is used to find the index of a particular string from a list of strings. This function accepts a string as the first parameter and a list of string values as subsequent parameters.

If the specified string is not found in the list, this function returns 0. Even if you pass NULL as the first parameter, the function will return 0.

Syntax

Following is the syntax of MySQL FIELD() function −

FIELD(str,str1,str2,str3,...)

Parameters

This function takes a search string and a list of strings as parameter.

Return Value

This function returns the index of the first occurrence of the search string in the list or 0 if not found.

Example

In the following example, we are using the FIELD() function to find the position of 'JavaFX' within a list of strings −

SELECT FIELD('JavaFX', 'Java', 'JavaFX', 'OpenCV', 'WebGL');

Following is the output of the above code −

FIELD('JavaFX', 'Java', 'JavaFX', 'OpenCV', 'WebGL')
2

Example

If we search for a string that is not there in the list of strings, the field() function returns 0 −

SELECT FIELD('unknown', 'Java', 'JavaFX', 'OpenCV', 'WebGL');

Output of the above code is as shown below −

FIELD('unknown', 'Java', 'JavaFX', 'OpenCV', 'WebGL')
0

Example

If we search for a NULL value in the list, the FIELD() function returns 0 −

SELECT FIELD(NULL, 'Apple', 'Orange', 'Mango', 'Banana');

The output obtained is as follows −

FIELD(NULL, 'Apple', 'Orange', 'Mango', 'Banana')
0

Example

You can also pass list of numbers as arguments to this function and search for desired value −

SELECT FIELD(25, 225, 669, 557, 25, 66);

The result produced is as follows −

FIELD(25, 225, 669, 557, 25, 66)
4

Example

Let us create a table named "FRUITS" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE FRUITS (
   id int AUTO_INCREMENT,
   name varchar(50) NOT NULL,
   color varchar(50) NOT NULL,
   PRIMARY KEY (id)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO FRUITS (name, color) VALUES 
('banana', 'yellow'),
('apple', 'red'),
('orange', 'orange'),
('kiwi', 'brown'),
('grape', 'purple'),
('watermelon', 'green');

The FRUITS table obtained is as follows −

id	name	color
1	banana	yellow
2	apple	red
3	orange	orange
4	kiwi	brown
5	grape	purple
6	watermelon	green

Here, we are using the FIELD() function to find the position of each fruit color in a list of possible colors −

SELECT name, color, FIELD(color, 'red', 'yellow', 'green', 'purple') AS color_ranking 
FROM FRUITS;

After executing the above code, we get the following output −

name	color	color_ranking
banana	yellow	2
apple	red	1
orange	orange	0
kiwi	brown	0
grape	purple	4
watermelon	green	3