MySQL – Search For Values Within A Comma Separated Values – FIND_IN_SET

MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. It basically returns the index position of the first parameter within the second parameter. This can be alternatively used to replace the IN clause.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);


Suppose you want to find out all sales made by Mobile and laptop. We can use IN clause as below
SELECT * FROM items NATURAL JOIN sales
WHERE item_description IN ('Mobile','laptop');

However you can also use FIND_IN_SET function to acheive the same

SELECT * FROM items NATURAL JOIN sales
WHERE FIND_IN_SET (item_description,'Mobile,laptop');

FIND_IN_SET accepts two parameters (first is column and second is column values seperated by commas).

Both the queries produce the following result

2 Mobile 2 2014-01-02 00:00:00 200.00
 3 laptop 3 2014-01-09 00:00:00 1700.00
 3 laptop 4 2014-01-29 00:00:00 1700.00
 3 laptop 5 2014-02-11 00:00:00 1700.00
 2 Mobile 7 2014-02-16 00:00:00 200.00
 2 Mobile 8 2014-02-20 00:00:00 200.00
 2 Mobile 9 2014-02-20 00:00:00 200.00
 2 Mobile 10 2014-02-22 00:00:00 200.00
 3 laptop 11 2014-02-24 00:00:00 1700.00

Well, technically it does not matter which option we use both the solution produces almost same results and performance, it is just an alternate way to search for values within a comma separated values.

Reference: Pinal Dave (https://blog.sqlauthority.com)