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:

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:

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

Syntax 3:

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:

Output:

Position_of_S
6

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

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:

The following CREATE statement creates the Fresher_Marks table:

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

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


Fresher_IDFirst_NameMiddle_NameLast_NameCityAptitude_MarksReasoning_MarksTechnical_MarksPercentage
501VinayRoyGuptaLucknow85927885
502MonuRoySinghaniaChandigarh54689888
504RaviRoyKumarLucknow71826971
507ShyamRoySharmaDelhi85906878
510AbhayKumarGuptaChandigarh45688278
509RiyaRoySharmaDelhi68906991
505VishalKumarSharmaMumbai75658875

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:

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

Output:

First_NameMiddle_NameLast_NamePosition_of_Kumar
VinayRoyGupta0
MonuRoySinghania0
RaviRoyKumar3
ShyamRoySharma0
AbhayKumarGupta2
RiyaRoySharma0
VishalKumarSharma2

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:

Output:

Fresher_IDAptitude_MarksReasoning_MarksTechnical_MarksPosition_of_68
5047182690
5078590683
5104568822
5096890691
5057565880