MySQL FIELD function is one of the String Functions, which returns the index position of the string (specified in the first argument) by looking at the remaining expression(s). If all the arguments present in it are integers, then they are compared as numbers. If they are strings, then they are compared to sentences or words.
The basic syntax of the MySQL String FIELD function 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’) returns 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 argument.
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');
