What would be the effect on MySQL output if we have the combination of NULL and other values in the list of strings, provided as arguments in FIELD() function?

There will be a significant change in the output if we have the combination of NULL and other values in the list of strings, provided as arguments in FIELD() function. Following example will demonstrate it

Example

mysql> Select FIELD('good','Ram','is','good','boy');
+---------------------------------------+
| FIELD('good','Ram','is','good','boy') |
+---------------------------------------+
|                                     3 |
+---------------------------------------+
1 row in set (0.00 sec)

As we can observe from the above output the index number of search string ‘good’ is 3. Now, if we will add NULL in the list of string then there will be a significant change in the output as follows −

mysql> Select FIELD('good','Ram','is',NULL,'good','boy');
+---------------------------------------------+
| FIELD('good','Ram','is',NULL,'good','boy')  |
+---------------------------------------------+
|                                           4 |
+---------------------------------------------+
1 row in set (0.00 sec)

It means that MySQL will compare the search string with NULL also.

Sai Subramanyam

Published on 20-Feb-2018 06:09:14