MySQL: can I do a for-each on a comma separated field?

I just ran into a problem.

I know these integers, $integers: 3,5,15,20.

I only want to select the rows from this following table where all comma separated INT's from the field NUMBERS are found.

TABLE: number_table
Uid Numbers
------------------------
1   3,5,15    OK, since all of NUMBERS are in $integers
2   5,15,20   OK, since all of NUMBERS are in $integers
3   3,4,5,15  NOT OK, since 4 is not found in $integers
4   2,15,20,25  NOT OK, since 2 and 25 is not found in $integers

Is it possible to do a "for-each" on a comma separated string or another way to do this SELECT?

UPDATE: It sounds like this is not possible. I will leave it here for little while. Just a hint. When searching for something in a comma separated string then MySQL provides the WHEERE something IN (comma separated string). What I What I look for is someway to traverse a comma separated string using MySQL but that might not be possible.

Something like this would do it (pseudocode):

SELECT * FROM number_table WHERE each_commaseparated_substring(Numbers , 'I开发者_JS百科N (3,5,15,20)')

---

It should NOT be comma separated fields. It must be rows in the related table.

---

I haven't tried this, and it's a bit ugly and quite possibly slow but you can try the following.

3,5,15,20

SELECT * FROM number_table
WHERE Numbers (LIKE '%,3,%' OR LIKE '%3,%') AND Numbers LIKE '%,5,%' AND Numbers LIKE '%,15,%' AND Numbers (LIKE '%,20,%' OR LIKE '%,20%')

You may be able to do something with REGEX. But at the very least you could use a stored procedure.

Updated for correctness

---

Maybe try with concate code using PHP and the implode() function.

---

Correct the short answer is no, but despite being non-normal data there are solutions that are ugly so not recommended. Specifically make a split string function and loop through each value with a stored procedure.

• Can MySQL split a column?
• Mysql string split

---

You could check that the number of commas is one less than the number of search terms found:

SELECT * FROM number_table
WHERE CHAR_LENGTH(Numbers) - CHAR_LENGTH(REPLACE(Numbers, ',', '')) = -1
  + (FIND_IN_SET( 3, Numbers) > 0)
  + (FIND_IN_SET( 5, Numbers) > 0)
  + (FIND_IN_SET(15, Numbers) > 0)
  + (FIND_IN_SET(20, Numbers) > 0)

To create this from $integers using PHP:

$sql = "SELECT * FROM number_table
        WHERE CHAR_LENGTH(Numbers) - CHAR_LENGTH(REPLACE(Numbers, ',', '')) = -1";

foreach ($integers as $i) $sql .= " + (FIND_IN_SET($i, Numbers) > 0)";