You will learn how to use FIND_IN_SET and IN in this tutorial.
FIND_IN_SET: It is built-in function which is used to find the position of a string within a comma-separated list of strings.
value: String value to search in list.
list: Comma separated string list.
FIND_IN_SET returns null if value or list is null.
Ex: SELECT FIND_IN_SET(NULL,’a,b,c,d’)
FIND_IN_SET returns 0 if value not found in list.
Ex: SELECT FIND_IN_SET(‘g’,’a,b,c,d’)
FIND_IN_SET return position of value in list, if value is found in list.
Ex: SELECT FIND_IN_SET(‘d’,’a,b,c,d’)
IN: IN operator is used to find value from list of values.
Ex: Employee table is mention below.
SELECT * FROM emp WHERE salary IN (20000,15000)
Example1: SELECT * FROM emp WHERE salary IN (‘15000,20000,10000’);
if you see the table above, there are multiple values present in table and there are three salary in ‘15000,20000,10000’ but it is returning only one value. Why this happens. It is because when we are searching in list of string value then it returns first matched record only.
Example2: SELECT * FROM emp WHERE FIND_IN_SET(salary,’15000,20000,10000′);
You can see the differences in above two example.
- Use FIND_IN_SET to find position of string from comma separated list of strings
- Use IN to find value from list of values.
- Never use IN to find position of string from comma separated list of strings, because you will get first element from list.
Please provide your valuable comments and suggestions.
Thanks for reading. Happy coding