MySQL - WHERE IN with a list into a field

Question

I have a field ('roles') with this values

        roles
row_1: 1,2,3,5
row_2: 2,13

I do this:

SELECT * FROM bloques WHERE 2 IN (roles)

and only find row_2, because it starts by 2

The LIKE option doesn't work because if I find 1

SELECT * FROM bloques WHERE roles LIKE '%1%'

, it gives me both rows.

What is your requirement? I cant follow.. What value you expect? And why? — nawfal, Dec 19 '12 at 14:26
Don't mind all the answers below, the only valid answer is: Read up on database normalization and trash your database design! — fancyPants, Dec 19 '12 at 14:37
duplicate http://stackoverflow.com/questions/13928538/select-row-which-contains-exact-number-in-column-with-set-of-numbers-separated-b — Sir Rufo, Dec 19 '12 at 14:42

score 3 · Answer 1 · answered Dec 19 '12 at 14:28

3

FIND_IN_SET function cal helps you:

SELECT FIND_IN_SET('b','a,b,c,d');

For your code:

SELECT * FROM bloques WHERE FIND_IN_SET(2,roles);

Also, I suggesto to you that move your schema to 1NF

answered Dec 19 '12 at 14:28

dani herrera

48,760
8
117
177

score 0 · Answer 2 · answered Dec 19 '12 at 14:28

0

Could you use REGEXP? http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Otherwise, you could add commas to the front and end of your rows.

select * from mytable where ',' + roles + ',' like '%,2,%'

answered Dec 19 '12 at 14:28

Sam

9,933
12
68
104

score 0 · Answer 3 · answered Dec 19 '12 at 14:30

0

SELECT 
  * 
FROM 
  bloques 
WHERE 
  roles LIKE '%,2,%' 
  OR roles LIKE '2,%' 
  OR roles LIKE '%,2'

The first case will give you all cases where 2 is in the middle of a set, the second case is when 2 starts the set, and the third case is when 2 ends the set. This is probably terribly inefficient, but it should work.

answered Dec 19 '12 at 14:30

Valdogg21

1,151
4
14
24

... and you need fourth case for '2' alone. There's a better way to do it. – raina77ow Dec 19 '12 at 14:37

Saharsh Shah · Answer 4 · 2014-12-09T06:01:19.920

0

Use FIND_IN_SET() function

Try this:

SELECT * FROM bloques WHERE FIND_IN_SET(1, roles);

OR

SELECT * FROM bloques 
WHERE CONCAT(',', roles, ',') LIKE '%,1,%';

edited Dec 09 '14 at 06:01

answered Dec 19 '12 at 14:30

Saharsh Shah

28,687
8
48
83

raina77ow · Answer 5 · 2012-12-19T14:38:34.820

You can use this regex here:

SELECT * FROM bloques 
  WHERE roles REGEXP '[[:<:]]2[[:>:]]';

... or, in more generic case:

SELECT * FROM bloques 
  WHERE roles REGEXP CONCAT('[[:<:]]', :your_value, '[[:>:]]');

You need to use these weird-looking things, as they match word boundaries - preventing match for '2' to occur at '23' and '32'. )

The problem is that query is only the beginning of the troubles caused by using denormalized field. I'd suggest using either SET type (if the number of options is limited and low) or, way better, junction table to store the m-n relationships.

MySQL - WHERE IN with a list into a field

5 Answers5