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

• Related Questions & Answers
• What MySQL returns if the list of strings, provided as argument in FIELD() function, are NULL?
• What would be the effect on the output of MySQL LAST_INSERT_ID() function in the case on multiple-row insert?
• What would be the result if we perform any kind of arithmetic calculations in MySQL having one of the arguments as NULL?
• What would be the output of MySQL ELT() function if the index number, provided as an argument, is not an integer?
• What MySQL returns if the search string is not in the list of strings provided as argument in FIELD() function?
• What is MySQL STRCMP() function and what would be the output of this function?
• What MySQL returns if we use NULL, as both the arguments, as one of the argument and as a separator, in CONCAT_WS() function?
• While linking the strings, if I will add a NULL value then what would be the output of a CONCAT_WS() function?
• What MySQL returns if the search string, provided in FIELD() function, is NULL?
• What would be the output if we use a NULL value in an arithmetic expression?
• What would be effect of negative value of second argument, which specifies the number of decimal places, on the output of MySQL ROUND() function?
• What would be effect of negative value of second argument, which specifies the number of decimal places, on the output of MySQL TRUNCATE() function?
• What MySQL COALESCE() function returns if all the arguments provided to it are NULL?
• What would be the output of MySQL SUM() function if a column having no values has been passed as its argument?
• While linking two strings, if I will add a NULL value then what would be the output of a CONCAT() function?