'mysql check if numbers are in a comma separated list

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...

mysqllist


Solution 1:[1]

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:

Solution 2:[2]

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.

Solution 3:[3]

You Can Try As Like Following :

 SELECT * FROM table_name WHERE FIND_IN_SET('3', NUMBERS) AND  FIND_IN_SET('15', NUMBERS)

Solution 4:[4]

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.

Solution 5:[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 ;

Solution 6:[6]

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

Solution 7:[7]

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.

Solution 8:[8]

Try the following:

SELECT UID
FROM TABLE
WHERE
concat(",",NUMBERS,",") like "%,1,%" 
OR
concat(",",NUMBERS,",") like "%,15,%";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tony Stark
Solution 2 Björn
Solution 3 Md. Maruf Hossain
Solution 4 Saty
Solution 5
Solution 6 user5127939
Solution 7 mickmackusa
Solution 8 Harry

Related Questions