FIELD Function in SQL

The FIELD string function in Structured Query Language returns the position of the given string from the list of strings. If the given string is not found in the list of string, then FIELD function returns 0 in result.

Syntax of FIELD String Function

In SQL, we can use the FIELD function with the columns of the table, strings, and characters.

Syntax 1:

SELECT FIELD(Searched_value, Column_Name1, column_Name2, Column_Name3,……. Column_NameN) AS Alias_Name FROM Table_Name;

In this syntax, we used the FIELD function with existing table of SQL. Here, we have to define the name and columns of that table on which we want to perform FIELD function.

Syntax 2:

SELECT FIELD("Searched_String", "String1", "String2", "String3", …… "StringN") AS Alias_Name;

In this syntax, we used the FIELD function with list of strings.

Syntax 3:

SELECT FIELD("Searched_character", "Character_1", "character_2", "Character_3", ……., "Character_N") AS Alias_Name;

In this syntax, we used the FIELD function with list of characters.

Examples of FIELD String function

Example 1: The following query uses the FIELD function with the list of strings:

SELECT FIELD( ''S'', ''H'', ''I'', ''A'', ''P'', ''Q'', ''S'', ''R'', ''T'', ''V'', ''M'' ) AS Position_of_S;

Output:

Position_of_S
6

Example 2: The following query searches the "Old" string which is not present in the list of string:

SELECT FIELD(''Old'', ''New'', ''Delhi'', ''is'', ''the'', ''Capital'', ''of'', ''India'') AS Position_of_Old;

Output:

Position_of_Old
0

Example 3: The following SELECT query uses the FIELD function with the list of numbers:

Output:

Position_of_8
2

Example 4: This example uses the FIELD function with the table in Structured Query Language.
In this fourth example, we will create the new table through which we will perform the FIELD function with table values:

The following block shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
........,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Fresher_Marks table:

CREATE TABLE Fresher_Marks
(
Fresher_ID INT PRIMARY KEY,  
First_Name VARCHAR (100),  
Middle_Name Varchar(120),
Last_Name VARCHAR (200), 
City Varchar(120),
Aptitude_Marks INT, 
Reasoning_Marks INT, 
Technical_Marks INT,
Percentage INT
);

The below INSERT queries insert the records of freshers with marks and details in the Fresher_Marks table:

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (501, Vinay, Roy, Gupta, Lucknow, 85, 92, 78, 85 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (502, Monu, Roy, Singhania, Chandigarh, 54, 68, 98, 88 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (504, Ravi, Roy, Kumar, Lucknow, 71, 82, 69, 71 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (507, Shyam, Roy, Sharma, Delhi, 85, 90, 68, 78 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (510, Abhay, Kumar, Gupta, Chandigarh, 45, 68, 82, 78);

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (509, Riya, Roy, Sharma, Delhi, 68, 90, 69, 91 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (505, Vishal, Kumar, Sharma, Mumbai, 75, 65, 88, 75 );

The following SELECT statement displays the inserted records of the above Fresher_Marks table:

Fresher_ID	First_Name	Middle_Name	Last_Name	City	Aptitude_Marks	Reasoning_Marks	Technical_Marks	Percentage
501	Vinay	Roy	Gupta	Lucknow	85	92	78	85
502	Monu	Roy	Singhania	Chandigarh	54	68	98	88
504	Ravi	Roy	Kumar	Lucknow	71	82	69	71
507	Shyam	Roy	Sharma	Delhi	85	90	68	78
510	Abhay	Kumar	Gupta	Chandigarh	45	68	82	78
509	Riya	Roy	Sharma	Delhi	68	90	69	91
505	Vishal	Kumar	Sharma	Mumbai	75	65	88	75

Query 1: The following SELECT query uses the FIELD function with the First_Name, Middle_Name, Last_Name columns of the above Fresher_Marks table:

SELECT First_Name, Middle_Name, Last_Name, FIELD('Kumar', First_Name, Middle_Name, Last_Name) AS Position_of_Kumar FROM Fresher_Marks;

This SQL statement shows the position of Kumar in the first name, middle name, and last name.

Output:

First_Name	Middle_Name	Last_Name	Position_of_Kumar
Vinay	Roy	Gupta	0
Monu	Roy	Singhania	0
Ravi	Roy	Kumar	3
Shyam	Roy	Sharma	0
Abhay	Kumar	Gupta	2
Riya	Roy	Sharma	0
Vishal	Kumar	Sharma	2

Query 2: The following SELECT query uses the FIELD function with the Aptitude_Marks, Reasoning_Marks, and Technical_Marks column of those Students whose Fresher_ID is greater than 502 in the above Fresher_Marks table:

SELECT Fresher_ID, Aptitude_Marks, Reasoning_Marks, Technical_Marks, FIELD(95, Aptitude_Marks, Reasoning_Marks, Technical_Marks) AS Position_of_68 FROM Fresher_Marks WHERE Fresher_ID > 502;

Output: