What is the difference between MySQL LOCATE() and FIND_IN_SET() functions?

MySQLMySQLi Database

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, LOCATE() function contains a string from which it will find the position of the first occurrence of the substring if present.
  •  In LOCATE() function we can manage the starting point of the search by providing an optional argument for a position. Whereas, for FIND_IN_SET() function MySQL do not provide such kind of flexibility and the search would by default starting from 1st string.
  •  In case of integers, FIND_IN_SET() is much more suitable than LOCATE() function. It can be understood by the following example

Example

mysql> Select IF(LOCATE(2,'10,11,12,13') > 0,1,0) As result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.05 sec)

mysql> Select IF(FIND_IN_SET(2,'10,11,12,13') > 0,1,0)As Result;
+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

From the result set of above examples, we can see that LOCATE() function returns 1 even 2 as a string is not present in the arguments. But FIND_IN_SET() function returns 0 which is correct answer.

raja
Monica Mona
Published on 20-Feb-2018 07:06:31
Previous Page Print Page
Next Page  
Advertisements