MySQL FIND_IN_SET() function
FIND_IN_SET() function
MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ‘,’ (comma) character.
This function returns 0 when search string does not exist in the string list and returns NULL if either of the arguments is NULL.
This function is useful in -
- It allows you to determine the position of a value within a comma-separated list.
- Filtering data: You can use FIND_IN_SET() in WHERE clauses to filter data based on the presence or absence of a value.
Syntax:
FIND_IN_SET (search string, string list)
Arguments:
Name | Description |
---|---|
search string | A string which is to be looked for in following a list of arguments. |
string list | List of strings to be searched if they contain the search string. |
Syntax Diagram:

MySQL Version: 8.0
Pictorial Presentation:
Example: FIND_IN_SET() function
The following MySQL statement finds the search string ‘ank’ at the 2nd place within the string list. So it returns 2.
Code:
SELECT FIND_IN_SET('ank','b,ank,of,monk');
Output:
mysql> SELECT FIND_IN_SET('ank','b,ank,of,monk'); +------------------------------------+ | FIND_IN_SET('ank','b,ank,of,monk') | +------------------------------------+ | 2 | +------------------------------------+ 1 row in set (0.00 sec)
MySQL FIND_IN_SET basic usage
Code:
SELECT * FROM fru_n_veg WHERE FIND_IN_SET('Cabbage', pkt_desc);
Sample table: fun_n_veg
Output:
item_id|weight|category |pkt_desc | -------+------+----------+--------------------------+ 5| 1500|Vegetables|Cabbage, Carrot, Broccoli | 8| 2500|Vegetables|Cabbage, Eggplant, Pumpkin|
This will return all rows where the vegetable 'Cabbage' is found in the pkt_desc column.
Example : MySQL FIND_IN_SET with WHERE CLAUSE
The following MySQL statement find the search string as defined first 7 characters from aut_name column from the table author within the given string as specified in the argument and retrieves all columns from the concern rows.
Code:
SELECT *
FROM author
WHERE FIND_IN_SET(left(aut_name,7),'William,steven,jung,angalo')>0;
Sample table: author
Output:
+--------+-----------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+-----------------+---------+-----------+ | AUT001 | William Norton | UK | Cambridge | | AUT002 | William Maugham | Canada | Toronto | | AUT003 | William Anthony | UK | Leeds | +--------+-----------------+---------+-----------+ 3 rows in set (0.13 sec)
Using with a specific value and condition
Code:
SELECT *
FROM fru_n_veg WHERE category = 'Fruits'
AND FIND_IN_SET('Guava', pkt_desc);
Sample table: fun_n_veg
Output:
item_id|weight|category|pkt_desc | -------+------+--------+-----------------------+ 1| 500|Fruits |Guava, Papaya, Pear | 2| 500|Fruits |Guava, Watermelon, Pear|
This will return items from the 'Fruits' category where the item 'Guava' found in their pkt_desc column.
Using with multiple values
Code:
SELECT * FROM fru_n_veg
WHERE FIND_IN_SET('Guava', pkt_desc)
OR FIND_IN_SET('Grapes', pkt_desc);
Sample table: fun_n_veg
Output:
item_id|weight|category|pkt_desc | -------+------+--------+-------------------------+ 1| 500|Fruits |Guava, Papaya, Pear | 2| 500|Fruits |Guava, Watermelon, Pear | 4| 1000|Fruits |Grapes, pear, Watermelon |
This will return rows which contains either 'Guava' or 'Grapes' in their pkt_desc column.
Using with dynamic values
Code:
SELECT * FROM fru_n_veg
WHERE FIND_IN_SET((SELECT item_name FROM items WHERE item_code = 2), pkt_desc);
Sample table: fun_n_veg
Sample table: items
Output:
item_id|weight|category |pkt_desc | -------+------+----------+--------------------------+ 5| 1500|Vegetables|Cabbage, Carrot, Broccoli | 8| 2500|Vegetables|Cabbage, Eggplant, Pumpkin|
This will return pkt_desc which contain the items specified by item_code 2 in their item_code column, with the item name fetched from the items table.
Using FIND_IN_SET() in an update statement
Code:
UPDATE fru_n_veg SET weight = 400 WHERE FIND_IN_SET('Dates', pkt_desc);
Sample table: fun_n_veg
The table have been updated. The data shows, in the item_id 6 row, the weight column have been updated by 400 for the pkt_desc 'Dates', which was 250.
item_id|weight|category |pkt_desc | -------+------+----------+--------------------------+ 1| 500|Fruits |Guava, Papaya, Pear | 2| 500|Fruits |Guava, Watermelon, Pear | 3| 1000|Vegetables|Potato, Onion, Ginger | 4| 1000|Fruits |Grapes, pear, Water melon | 5| 1500|Vegetables|Cabbage, Carrot, Broccoli | 6| 400|Dry Fruits|Dates, Almond, Dry fig | 7| 400|Dry Fruits|Walnuts, Almond, Cashew | 8| 2500|Vegetables|Cabbage, Eggplant, Pumpkin|
Difference between LOCATE() and FIND_IN_SET() Function
When using LOCATE() function for integers, suppose we need 1 to return from LOCATE() if integer 3 is in the set '1,2,3,4,5,..' the following MySQL commands can be written :
Code:
SELECT IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0);
Output:
mysql> SELECT IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0); +-----------------------------------------+ | IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0) | +-----------------------------------------+ | 1 | +-----------------------------------------+ 1 row in set (0.06 sec)
The above command working rightly because the set contains the number 3 , but if we write the following commands, look what happened -
Code:
SELECT IF(LOCATE(3,'11,12,13,14,15')>0,1,0);
Output:
mysql> SELECT IF(LOCATE(3,'11,12,13,14,15')>0,1,0); +--------------------------------------+ | IF(LOCATE(3,'11,12,13,14,15')>0,1,0) | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.02 sec)
Here we see from above example that, the 3 not present as a number three(3) in the given set, though the LOCATE() returns 1, because LOCATE() treate the given set as a string but not a comma seperated value, and the 3 present in the number 13.
To avoid this type of situation you can use the FIND_IN_SET() function. Here is the example below -
Code:
SELECT IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0);
Output:
mysql> SELECT IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0); +-------------------------------------------------+ | IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0) | +-------------------------------------------------+ | 0 | +-------------------------------------------------+ 1 row in set (0.05 sec)
So, LOCATE() function is very much suitable for string but not as much suitable for integer.
Video Presentation:
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook