0

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 , 'IN (3,5,15,20)')
Tillebeck
  • 3,493
  • 7
  • 42
  • 63

5 Answers5

3

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 2
    "must" is strong... but normalized data (one row per item in another table) is far preferable. – Jason S Apr 22 '10 at 11:44
  • Good "best practice". But several applications work with commaseparated values. And in my job I also have to be able to those applications databases to pull data if needed. I quite often find relations stored as comma separated values when only used for one-2-many relations and there rarely are done a reverse lookup. Pro: a simpler database diagram. Con: database not normalized – Tillebeck Apr 29 '10 at 12:36
1

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

galford13x
  • 2,483
  • 4
  • 30
  • 39
  • Hi. That is the other way around searching for all $integers in filed Numbers. It should be all comma separated int's from field Integers that should be found in $integers – Tillebeck Apr 22 '10 at 11:14
  • Second thing. It is not enough to seach for %,3,%. For each INT it has to be a seach for '%,3,%' OR '3,%' OR '%,3' so beginning and end of string also is checked. – Tillebeck Apr 22 '10 at 11:16
  • Otherwise. Thanks for you feedback and help :-) Sorry to sound so negative I really appreciate all the answers and comments. – Tillebeck Apr 22 '10 at 11:17
  • Forgot: When searching a comma separated string you should us IN and not LIKE. See the page about a third down: http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3 – Tillebeck Apr 22 '10 at 11:31
  • 1
    @Tilebeck: You are correct that %,3,% OR %3,% as well as %,20,% OR %,20% would be needed. However I would like to see you reconstruct the select state using the 'IN' clause. By what you said, the LIKE operator should never be used and shoudl always be replaced with the 'IN' clause and this is a false statement. Let me ask you this, can a wildcard be used with the 'IN' clause? – galford13x Apr 22 '10 at 14:08
  • sure, you are right. The same select cannot be used with IN. It will take another select statement. I was to stuck on the thought of iterating through each value in numbers and doing an IN on the $integer while iterating. Sorry. – Tillebeck Apr 29 '10 at 12:50
  • Perhaps you should remove the downvotes now you realise these are valid solutions to your problem. – Andy Apr 29 '10 at 13:11
  • Yep. I started out misunderstanding the answer. It is a correct sql call for a single row. But I need a call to get * from number_table (and not just a single row where I on forehand know the field Numbers). From the help given it seems it has to be done in two calls, therefore using php or similar between calls – Tillebeck May 03 '10 at 14:21
  • Never commented on the stored procedure. That may be a solution but I just do not know how to do that using MySQL/phpmyadmin – Tillebeck May 03 '10 at 14:26
  • It's been a while since I've looked at mysql stored procedures. You may want to start another question that asks for that specifically. The sql select statement I put should select all rows that match the 'LIKE' criteria shown. In other words it should return all rows that contain all of 3,5,15,20. If it is not doing so, let me know as I may be missing somthing. – galford13x May 03 '10 at 17:15
0

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.

Community
  • 1
  • 1
KCD
  • 9,873
  • 5
  • 66
  • 75
0

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)";
eggyal
  • 122,705
  • 18
  • 212
  • 237
0

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Karthik
  • 3,221
  • 5
  • 28
  • 38
  • It seems like it is not possible to do in one call. Guess you are right, that I have to use php. If I find a better solution I will post it here and zap you right answer – Tillebeck May 03 '10 at 14:29