0

Let's say I have a table like this:

id | set_of_numbers
-------------------------
1  | "41,52,64,238,246"
2  | "41,52,64"
3  | "41,52,64,238,245,246,248,323,418"
4  | "89,52,64"    
5  | "31,52,64,128,246"
6  | "41,52,64"
7  | "41,52,64,238,245,248,323,418"
8  | "67,53,64"

Now I have a query set like (64,246) and I want to list all items that have ALL numbers in the query set. So the result will be (1,3,5).

I'm using MySQL Procedure where the input is (64,246) so I don't have the numbers separated.

nbanic
  • 1,270
  • 1
  • 8
  • 11
Rafael D'Arrigo
  • 110
  • 9
  • It's almost ever a bad idea to store comma separated lists in columns. That said, it should be easy to split in your procedure the input to a list of numbers and use FIND_IN_SET to find those in your second column. – VMai May 09 '14 at 19:51

1 Answers1

0

I think you need something like this:

SELECT * FROM mytable
WHERE 
(set_of_numbers LIKE '%64,' 
 OR set_of_numbers LIKE '%,64,%' 
 OR set_of_numbers LIKE ',64%' )
AND 
(set_of_numbers LIKE '%246,'
 OR set_of_numbers LIKE '%,246,%'
 OR set_of_numbers LIKE ',246%')

Here's a link to a similar example, and here's a link to w3school's explanation on the LIKE operator for your reference :)

Community
  • 1
  • 1
tony
  • 88
  • 8