SQL Select where id is in `column`

Question

I have a column that has multiple numbers separated by a comma. Example for a row:

`numbers`:
 1,2,6,66,4,9

I want to make a query that will select the row only if the number 6 (for example) is in the column numbers.

I cant use LIKE because if there is 66 it'll work too.

You shouldn't be storing comma separated values to begin with — a_horse_with_no_name, Jul 07 '18 at 11:21
@a_horse_with_no_name I know. That's an old question that I edited for fixing grammar. Most likely I needed to normalize the data structure... — HTMHell, Jul 07 '18 at 13:33

score 7 · Accepted Answer · answered May 02 '13 at 19:52

7

You can use like. Concatenate the field separators at the beginning and end of the list and then use like. Here is the SQL Server sytnax:

where ','+numbers+',' like '%,'+'6'+',%'

SQL Server uses + for string concatenation. Other databases use || or the concat() function.

answered May 02 '13 at 19:52

Gordon Linoff

Adding the commas to the original string is both effective and concise. I dig it. – Maple May 02 '13 at 20:20

score 1 · Answer 2 · answered May 02 '13 at 19:53

You should change your database to rather have a new table that joins numbers with the row of your current table. So if your row looks like this:

id   numbers
 1   1,2,6,66,4,9

You would have a new table that joins those values like so

row_id   number
1        1
1        2
1        6
1        66
1        4
1        9

Then you can search for the number 6 in the number column and get the row_id

2 Answers2