Can I search for 1 AND 2 AND 3 without getting a false match because 11,22,33 are in the field?
A field that has the string = "11,22,33" should then not return any results.
+2
A:
Use regular expression matching with the regexes (^|,)1($|,) and (^|,)2($|,) and (^|,)3($|,)
Jim Garrison
2010-01-07 19:08:12
+2
A:
First of all, using comma separated values in a field is problematic, and you should consider storing them in a sepatate table instead. Then you could get the record more efficiently:
select ...
from mainTable t
inner join valueTable v1 on v1.id = t.id and v1.value = 1
inner join valueTable v2 on v2.id = t.id and v2.value = 2
inner join valueTable v3 on v3.id = t.id and v3.value = 3
If that is not possible, you have to go the slow string matching way. To match the values in a comma separated string, you can use the like operator:
... where
concat(',', someField, ',') like '%,1,%' and
concat(',', someField, ',') like '%,2,%' and
concat(',', someField, ',') like '%,3,%'
Putting the separator on both sides of the searched value makes sure that you don't get any false positives. Adding the commas before and after the field value makes sure that you can find the first and last value.
Guffa
2010-01-07 19:15:56
Yeah Just what I thought. With separate table is that I seemingly can't do the select I want. SELECT * FROM table WHERE id = '1' AND id = '2' AND id = '3'. The table would in this case be a pivot table: id, user_id, option_id
Cudos
2010-01-07 19:21:38
@Cudos: You can easily get the result using a separate table. See the query example that I added above.
Guffa
2010-01-07 19:36:48
@Guffa: I was also thinking about something like this: `SELECT t.name FROM maintable t INNER JOIN valueTable v ON v.value IN (1, 2, 3) AND t.id = v.id GROUP BY t.name HAVING COUNT(*) >= 3` although I haven't tested it.
R. Bemrose
2010-01-07 19:56:15
@R.Bemrose: Yes, that would also work, but I think that it's slower as all records in the table has to be grouped, compared to gradually elliminating records using joins.
Guffa
2010-01-07 20:01:46
+1
A:
Have you considered what an optimal search for the value 11 would do in your case?
Since there's no way to narrow down the search, it is doomed to perform a table-scan to find the relevant values.
You should seriously consider splitting those values up into a separate table, where you have one row per value, all linked back to the original row in the original table.
This would be far more correct, far more performant, and far easier to deal with.
Having said that, if you still want to use your current approach, you can search for a value by doing this:
WHERE ','+ column + ',' LIKE '%,' + value + ',%'
This will search ',11,22,33,' for '%,11,%', note the commas at each end of both the column and the value, this will ensure you don't get false positives due to partial matches.
Lasse V. Karlsen
2010-01-07 19:24:05
From comment below: With separate table is that I seemingly can't do the select I want. SELECT * FROM table WHERE id = '1' AND id = '2' AND id = '3'. The table would in this case be a pivot table: id, user_id, option_id. How would you deal with that?
Cudos
2010-01-07 19:35:34
Assuming you split the field up into a separate table, you mean you want the users for which option 1, 2 and 3 are all present, is that it?
Lasse V. Karlsen
2010-01-07 20:21:37
Yes. You are correct - both in your assumption and above code. But as you and above poster pointed out making a separate table should be done. After seeing his SELECT I went with the recommended solution.
Cudos
2010-01-07 21:26:01
A:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
FIND_IN_SET(1,column) AND FIND_IN_SET(3,column) AND etc;
Its designed to be used with the SET type, but it also works with a basic comma seperated list.
MindStalker
2010-01-07 19:45:12