FIELD Function

The FIELD function is an advanced SQL topic that returns the position of a given string in a list of strings. This function is used when you want to know the position of a specific string in a list of strings or when you want to sort the result set in a specific order.

Syntax

The syntax of the FIELD function is as follows:

FIELD(string, string_list)

The string argument is the string to search for in the string_list. The string_list argument is a comma-separated list of strings to search.

Example

Consider the following example:

SELECT 
  name, 
  FIELD(name, 'John,Jack,Mary,Jane') AS position 
FROM 
  employees 
ORDER BY 
  position;

This SQL query returns the name of each employee in the employees table, along with the position of each name in the string_list: "John, Jack, Mary, Jane". The result set appears in alphabetical order of position.

Output

The output of the above SQL query might appear as follows:

name	position
Jack	2
Jane	4
John	1
Mary	3

Explanation

The FIELD function returns the position of the first occurrence of the string argument in the string_list argument. In the above example, the name "Jack" is second in the string_list, hence its position value is 2.

Use

The FIELD function is used when we need to order the result set in a specific order, based on the position of each item in a string_list.

Important Points

• The FIELD function is case-sensitive.
• If the string argument is not found in the string_list, the function returns 0.
• The string_list argument must be provided as a comma-separated list of strings.

Summary

In summary, the FIELD function is a powerful SQL function that returns the position of a given string in a list of strings. It is used when we need to sort a result set in a specific order.