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

FIND_IN_SET searches a comma-separated string for a complete member and returns its position. I keep whitespace in that string deliberate.

My MySQL example compares Mobile and laptop sales using IN and FIND_IN_SET. It keeps three products and twelve sales rows. Remove the space before laptop in the CSV string. That space becomes part of the member and changes the match.

CREATE TEMPORARY TABLE items (item_id int PRIMARY KEY, item_description varchar(100));
CREATE TEMPORARY TABLE sales (sales_id int AUTO_INCREMENT PRIMARY KEY, item_id int, sales_date datetime, sales_amount decimal(12,2));
INSERT INTO items VALUES (1,'Television'),(2,'Mobile'),(3,'laptop');
INSERT INTO sales (item_id,sales_date,sales_amount) VALUES
 (1,'2014-01-01',1200),(2,'2014-01-02',200),(3,'2014-01-09',1700),
 (3,'2014-01-29',1700),(3,'2014-02-11',1700),(1,'2014-02-16',1200),
 (2,'2014-02-16',200),(2,'2014-02-20',200),(2,'2014-02-20',200),
 (2,'2014-02-22',200),(3,'2014-02-24',1700),(1,'2014-02-24',1200);
SELECT i.item_id,i.item_description,s.sales_id,s.sales_date,s.sales_amount
FROM items AS i JOIN sales AS s ON s.item_id = i.item_id
WHERE i.item_description IN ('Mobile','laptop') ORDER BY s.sales_id;
SELECT i.item_id,i.item_description,s.sales_id,s.sales_date,s.sales_amount
FROM items AS i JOIN sales AS s ON s.item_id = i.item_id
WHERE FIND_IN_SET(i.item_description,'Mobile,laptop') > 0 ORDER BY s.sales_id;
DROP TEMPORARY TABLE sales;
DROP TEMPORARY TABLE items;

The corrected queries select nine sales: IDs 2, 3, 4, 5, 7, 8, 9, 10 and 11. The product names and results are unchanged. An explicit JOIN avoids NATURAL JOIN adding an unexpected condition after a schema change.

Understand the string format

The position starts at 1. A missing member or empty list returns 0; a NULL argument returns NULL. A search member containing a comma is not handled as one escaped CSV field. This is not a general CSV parser.

Keep actual relationships in rows rather than assuming a comma-separated column is the best schema. Performance equivalence requires a measured workload and plan; the original identical-speed claim was not established.

Reference: MySQL FIND_IN_SET.

FIND_IN_SET is not a general CSV parser, it is a function for matching members in a comma-separated string.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.