In MySQL, how FIELD() function is different from FIND_IN_SET() function?

As we know, both the functions are used to search a string from the arguments provided in them but there are some significant differences between them as follows −

FIND_IN_SET() −  function uses the string list that is itself a string containing the substring separated by commas. Whereas, FIELD() function contains list of different strings among which it will find the index number of the string, if present, which is to be searched.

FIND_IN_SET() −  function returns NULL if any of the argument i.e. either search string or string list is NULL. In contrast, FIELD() function do not returns NULL but returns 0 if search string is NULL.

Example

mysql> Select FIND_IN_SET(NULL,'Ram is a good boy') AS Result;

+--------+
| Result |
+--------+
| NULL   |
+--------+

1 row in set (0.00 sec)

mysql> SELECT FIND_IN_SET('RAM',NULL)AS RESULT;

+--------+
| RESULT |
+--------+
| NULL   |
+--------+

1 row in set (0.00 sec)

mysql> Select FIELD(NULL,'Ram','is','good','boy');

+-------------------------------------+
| FIELD(NULL,'Ram','is','good','boy') |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

1 row in set (0.00 sec)

Arjun Thakur

e

Updated on: 20-Jun-2020

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started