MySQL FIELD Function

MySQL FIELD function is one of the String Functions, which returns the index position of the string (specified in the first argument) by looking in the remaining expression(s). If all the arguments present in the MySQL string FIELD function are integers, then they are compared as numbers. If they are strings, then they are compared to the sentences or words.

MySQL FIELD Syntax

The basic syntax of String FIELD in MySQL is as shown below:

SELECT FIELD(Str, Str1, Str2,....,StrN)
FROM Source

The above syntax returns the index position of the Str by checking Str1 to StrN based. For example, ELT(‘Hello’, ‘Hi’, ‘Hello’) return 2 as the output.

MySQL FIELD Example

The FIELD function returns the index position of the specified characters or words. The following query shows various ways to use this one.

-- Though the position 1 start with A, it is not equal to A
SELECT FIELD('A', 'ABC', 'DEF', 'G', 'H', 'I');

SELECT FIELD('MySQL', 'Learn', 'MySQL', 'Server', 'at', 'tutorialgateway.org');

SELECT FIELD('tutorialgateway.org', 'Learn', 'MySQL', 'Server', 'at', 'tutorialgateway.org');

-- It returns 0 because there is no 'at'
SELECT FIELD('at', 'Learn', 'MySQL', 'Server');

-- It will return the index position of First Occurence
SELECT FIELD('MySQL', 'Learn', 'MySQL', 'Server', 'at', 'MySQL');

Let me show you what happens if we specify integers and MySQL NULL values as this method arguments.

SELECT FIELD('2', '12', '13', '2', '15', '19');

SELECT FIELD(2, 12, 13, 2, 15, 19);

SELECT FIELD(2, 22, 222, 2222);

SELECT FIELD(NULL, 'Learn', NULL, 'MySQL', 'Tutorial');