FIND_IN_SET with multiple value

Question

I want to search multiple values from database field. below is my query.

SELECT * FROM `tablename` 
WHERE FIND_IN_SET('12,13,15,15',category_id) 

How i search its not working for me.

Answer 1

FIND_IN_SET() can only be used to search for a single value in a comma-separated list, it doesn't work with two lists.

You'll need to call it separately for each value.

SELECT * FROM tablename
WHERE FIND_IN_SET('12', category_id) OR FIND_IN_SET('13', category_id) OR FIND_IN_SET('15', category_id)

It would be better if you normalized your schema instead of using comma-separated lists. If you create a many-to-many table with the category IDs, you could do:

SELECT t1.*
FROM tablename AS t1
JOIN item_categories AS c ON t1.id = c.table_id
WHERE c.category_id IN (12, 13, 15)

Answer 2

I had a similar need. To solve this I created a couple of stored functions:

The first one is all_in_set(a,b), and checks if all items in a are in b:

delimiter $$

drop function if exists all_in_set;

CREATE FUNCTION all_in_set(set1 varchar(1024),set2 varchar(1024)) RETURNS boolean
    NO SQL
    DETERMINISTIC
    COMMENT 'verify if all of the items in set1 are in set2'

BEGIN


declare i int default 0;
declare c varchar(128);
declare campos1 int;


set campos1=length(set1) - length(replace(set1, ',', '')) + 1;
set i=1;



while i<=campos1 do
    set c=SUBSTRING_INDEX(SUBSTRING_INDEX(set1, ',', i), ',', -1);
    if find_in_set(c,set2)=0 then
        return false;
    end if;
    set i=i+1;
end while;


return true;

END$$

delimiter ;

for testing you can use:

> select all_in_set('a,b,c','a,b,c,d,e,f'),all_in_set('a,x,c','a,b,c,d,e,f');
+-----------------------------------+-----------------------------------+
| all_in_set('a,b,c','a,b,c,d,e,f') | all_in_set('a,x,c','a,b,c,d,e,f') |
+-----------------------------------+-----------------------------------+
|                                 1 |                                 0 |
+-----------------------------------+-----------------------------------+

The second ne is any_in_set(a,b) which returns true if at least one of the items in a is included in b.

delimiter $$

drop function if exists any_in_set;

CREATE FUNCTION any_in_set(set1 varchar(1024),set2 varchar(1024)) RETURNS boolean
    NO SQL
    DETERMINISTIC
    COMMENT 'verify if at least one of the items in set1 is in set 2'

BEGIN

declare i int default 0;
declare c varchar(128);
declare campos1 int;


set campos1=length(set1) - length(replace(set1, ',', '')) + 1;
set i=1;


while i<=campos1 do
    set c=SUBSTRING_INDEX(SUBSTRING_INDEX(set1, ',', i), ',', -1);
    if find_in_set(c,set2)<>0 then
        return true;
    end if;
    set i=i+1;
end while;


return false;

END$$

delimiter ;

for testing you can use:

> select any_in_set('x,y,c','a,b,c,d,e,f'),any_in_set('x,y,z','a,b,c,d,e,f');
+-----------------------------------+-----------------------------------+
| any_in_set('x,y,c','a,b,c,d,e,f') | any_in_set('x,y,z','a,b,c,d,e,f') |
+-----------------------------------+-----------------------------------+
|                                 1 |                                 0 |
+-----------------------------------+-----------------------------------+
1 row in set (0.018 sec)

Answer 3

FIND_IN_SET is not the solution. Try to use REGEXP:

SELECT * FROM `tablename` 
WHERE CONCAT(',', `category_id`, ',') REGEXP ',(12|13|15),';

But even if it's less pretty, it's better to use LIKE for performance reasons :

SELECT * FROM `tablename` 
WHERE CONCAT(',', `category_id`, ',') LIKE '%,12,%' OR CONCAT(',', `category_id`, ',') LIKE '%,13,%' OR CONCAT(',', `category_id`, ',') LIKE '%,15,%';