CODEWITHSUNDEEP
sqlcsv
HTMHell
HTMHell

Reputation: 6006

SQL Select where id is in `column`

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.

Upvotes: 3

Views: 324

Answers (2)

juuga
juuga

Reputation: 1314

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 7

Related Questions