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 it are integers, then they are compared as numbers. If they are strings, then they are compared to the sentences or words.
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 Function Example
It 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');
