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

MySQL - Search For Values Within A Comma Separated Values - FIND_IN_SET manycommas

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)

MySQL
Previous Post
SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020
Next Post
Professional Development – Dr W. Edwards Deming’s 14 Principles on Total Quality Management

Related Posts

5 Comments. Leave new

  • How To use for multiple value .
    Ex. FIND_IN_SET(‘dev,ani’,’dev,ani,pulak’);
    How>

    Reply
  • WHERE FIND_IN_SET (item_description,’Mobile,laptop’);

    should be

    WHERE FIND_IN_SET (‘Mobile,laptop’, `item_description`);

    Reply
  • No its doesn’t work….

    Reply
  • SELECT * FROM tbl_job WHERE FIND_IN_SET(‘2’, `special_id`) or FIND_IN_SET(‘2,3’, `special_id`) but its not woking can u plz help for the same…

    Reply
  • i want to fetch comma separated record in mysql database
    i write this query.
    `select id,title,image from add_news where FIND_IN_SET(‘Travel’,’Music’,type)ORDER BY id DESC`
    Then

    i have this type of error

    Incorrect parameter count in the call to native function ‘FIND_IN_SET’

    please give a right query…

    Reply

Leave a Reply