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:

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:

All String Functions (Slides presentation)

Previous: FIELD
Next: FORMAT