Select all that have specific number

Question

I have a question for mysql gurus. I have a table like this:

     | col1  | col2         |
row1 | John  | 1,2,13,22,52 |
row2 | Dave  | 1,8,10       |
row3 | Maria | 2,3,5,11     |

How can I write a query that checks for specific number inside "col2". For example I want to have all rows that have number "1" in it not "13", or "11" but "1". So it would return row1 and row2.

I was trying to do that with "LIKE %" but that did not work

SELECT * FROM table WHERE col2 LIKE "%1%"

Any suggestions?

EDIT

Separate into two tables?

table1
     | id | col1  | 
row1 | 1  | John  | 
row2 | 2  | Dave  | 
row3 | 3  | Maria |

table2
     | id | f_id | col2 |
row1 | 1  | 1    | 1    |
row1 | 2  | 1    | 2    |
row1 | 3  | 1    | 13   |
row1 | 4  | 1    | 22   |
row1 | 5  | 1    | 52   |
row2 | 6  | 2    | 1    |
row2 | 7  | 2    | 8    |
row2 | 8  | 2    | 10   |
row3 | 9  | 3    | 2    |
row3 | 10 | 3    | 3    |
row3 | 11 | 3    | 5    |
row3 | 12 | 3    | 11   |

you should not have multiple values in one column. It violates normalization rules — Muhammad Usman
change your table structure and add separate column/table for each value — Muhammad Usman

cn007b cn007b · Accepted Answer · 2017-10-26T22:46:11

You have to use function FIND_IN_SET:

SELECT * FROM table WHERE FIND_IN_SET('1', col2) > 0;

In case you gonna use separated table:

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.f_id
WHERE table2.col2 = 1;

Select all that have specific number

1 Answers