31

I have a table like this:

UID(int) NUMBERS(blob)
----------------------
1        1,13,15,20
2        3,10,15,20
3        3,15

And I would like to test if 3 and 15 are in the blob called NUMBERS. And can see the LIKE %% cannot be used

Only row with ID 2 and three scoulb be selected...

Tillebeck
  • 3,493
  • 7
  • 42
  • 63
  • possible duplicate of [MySQL query finding values in a comma separated string](http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) – Salman A Mar 09 '15 at 12:06

8 Answers8

48

This one also works:

SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)

using the IN will look into a comma separated string eg. these two

WHERE banana IN ('apple', 'banana', 'coconut')
WHERE 3 IN (2,3,6,8,90)

Info found on this page:

Tony Stark
  • 8,064
  • 8
  • 44
  • 63
Tillebeck
  • 3,493
  • 7
  • 42
  • 63
  • feel bad about accepting own answer since the other was right too. But the IN seems more right than the find_in_set even though that would work too. – Tillebeck May 03 '10 at 07:14
  • 1
    Coding after a few beers caused me to land on this question. Very helpful thank you! – psx Apr 15 '12 at 20:59
  • 24
    Warning: This answer could be *wrong*, because [`foo IN ('1,2,3') ≡ foo IN (CAST('1,2,3' AS INT)) ≡ foo IN (1)`](http://stackoverflow.com/a/4156063/116546). I guess, `find_in_set` is more sane option. – drdaeman Jul 22 '12 at 14:36
  • 4
    As drdaeman noted, this does not work when the value being searched for is an INT unless the searched for value happens to be the first in the set. – JoshHetland Jun 20 '13 at 15:54
  • 1
    I have a view where a column contain comma separated values, the column was created using GROUP_CONCAT, and this solution doesn't seem to work in that scenario, but the find_in_set does, thought i may share my findings. – Q_ro Aug 26 '13 at 01:17
  • It doesn't work. I tested it against the cases where the searched value is: first, last and in the middle of the list. When comparing INT it gave me only one result, where the searched value is at the beginning of the csv list. Hence the only acceptable solution is Bjorn's one imo. – jszoja Aug 17 '18 at 11:50
  • find_in_set is more accurate answer – Norielle Cruz Feb 25 '22 at 02:43
40

Not the most pretty solution, but it works:

select
   UID
from
   YOUR_TABLE
where
   find_in_set('3', cast(NUMBERS as char)) > 0
   and
   find_in_set('15', cast(NUMBERS as char)) > 0

Note that it's string comparison, so you may need to cast your input parameters to char as well.

Björn
  • 29,019
  • 9
  • 65
  • 81
  • Thanks for your help. I found a page with an exmaple for seaching comma separated values. I posted it as an answer. Guess your solution would work fine too – Tillebeck Apr 20 '10 at 11:47
  • 1
    This worked for me where IN did not. Here is sample: select id from users where find_in_set(uid,(select editors from articles where article_id = 123)) – Johnathan Elmore Aug 27 '12 at 23:02
  • be aware, find_in_set working without index so might be slow – aswzen Aug 04 '21 at 19:53
21

You Can Try As Like Following :

 SELECT * FROM table_name WHERE FIND_IN_SET('3', NUMBERS) AND  FIND_IN_SET('15', NUMBERS)
Md. Maruf Hossain
  • 912
  • 9
  • 11
8

Also check if this is helpful to anyone

An Extended function to eliminate the limitation of native FIND_IN_SET() in MySQL, this new extended version FIND_IN_SET_X() provides feature to compare one list with another list.

i.e.

mysql> SELECT FIND_IN_SET_X('x,c','a,b,c,d'); -> 3

Checkout this link for more details.

Saty
  • 22,443
  • 7
  • 33
  • 51
Nilesh
  • 113
  • 1
  • 2
  • 1
    Anybody know where I can find details of this function? I can't find any references to it and the link no longer works... – Matthew Oct 22 '13 at 17:53
  • 4
    That function ´FIND_IN_SET_X´ doesn't exist. There are no references anywhere about it. – Memochipan May 11 '14 at 18:24
  • 1
    `FIND_IN_SET_X()` appears to be a user defined function. The [answer](http://stackoverflow.com/a/36775641/1089242) by @Rodolfo Souza is an example. – Lacek Oct 28 '16 at 01:36
5

The function complete for you

DELIMITER $$

CREATE FUNCTION `FIND_IN_SET_X`(inputList TEXT,targetList TEXT) RETURNS INT(11)
    DETERMINISTIC
BEGIN
  DECLARE limitCount INT DEFAULT 0 ;
  DECLARE counter INT DEFAULT 0 ;
  DECLARE res INT DEFAULT 0 ;
  DECLARE temp TEXT ;
  SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',', '')) ;
  simple_loop :
  LOOP
    SET counter = counter + 1 ;
    SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList, ',', counter),',',- 1) ;
    SET res = FIND_IN_SET(temp, targetList) ;
    IF res > 0 
    THEN LEAVE simple_loop ;
    END IF ;
    IF counter = limitCount 
    THEN LEAVE simple_loop ;
    END IF ;
  END LOOP simple_loop ;
  RETURN res ;
END$$

DELIMITER ;
Rodolfo Souza
  • 300
  • 4
  • 5
0

find_in_set_x

create a new function in mysql and paste in the following (not my work by the way)

BEGIN
DECLARE limitCount INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE res INT DEFAULT 0;
DECLARE temp TEXT;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',',''));
simple_loop:LOOP
SET counter = counter + 1;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList,',',counter),',',-1);
SET res = FIND_IN_SET(temp,targetList);
IF res > 0 THEN LEAVE simple_loop; END IF;
IF counter = limitCount THEN LEAVE simple_loop; END IF;
END LOOP simple_loop;
RETURN res;
END
user5127939
  • 1
0

Try This query :

SELECT UID FROM table WHERE NUMBERS REGEXP "[[:<:]](3|10)[[:>:]]"

[[:<:]],[[:>:]]: These markers stand for word boundaries, and as such, they match the beginning and ending of words, respectively.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Pradeep P
  • 1
0

Try the following:

SELECT UID
FROM TABLE
WHERE
concat(",",NUMBERS,",") like "%,1,%" 
OR
concat(",",NUMBERS,",") like "%,15,%";
Harry
  • 149
  • 2
  • 8