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

MySQLMySQLi Database

<p>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</p><ul class="list"><li>FIND_IN_SET() function uses the string list that is itself a string containing the substring separated by commas. Whereas, INSTR() function contains a string from which it will find the position of the first occurrence of the substring if present.</li><li>&nbsp;In case of integers, FIND_IN_SET() is much more suitable than INSTR() function. It can be understood by the following example</li></ul><h4>Example</h4><pre class="prettyprint notranslate">mysql&gt; Select IF(INSTR(&#39;10,11,12,13&#39;,2) &gt; 0,1,0) As Result; +--------+ | Result | +--------+ | &nbsp; &nbsp; &nbsp;1 | +--------+ 1 row in set (0.05 sec) mysql&gt; Select IF(FIND_IN_SET(2,&#39;10,11,12,13&#39;) &gt; 0,1,0)As Result; +--------+ | Result | +--------+ | &nbsp; &nbsp; &nbsp;0 | +--------+ 1 row in set (0.00 sec)</pre><p>From the result set of above examples, we can see that INSTR() 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.</p>
raja
Updated on 04-Feb-2020 06:03:15

Advertisements