Selecting from a comma delimited list 3

[dunxd]

I have a table with a field that contains a comma delimited list. The data for the table is input through an HTML form. The comma delimited list is created by checking check boxes on the form.

Eg[tt]
id | name | category
---|------|-----------
1 | bob | 1,10,11,14
2 | ted | 2,11,15
----------------------[/tt]
How do I write an SQL SELECT that will get rows that have a specific number in the comma delimited list? I don't think I can use SELECT * FROM table WHERE category LIKE "%1%" because that would select both bob and ted as the categories both contain 1.

Is there a function I can use that can see the list as distinct numbers delimited by commas, and then select all the ones that contain a specific number (or numbers) in the list? I know how I could do this in JavaScript or ASP, but I would rather SQL Server did the hard work.

I am sure this is a fairly common thing to want to do, but I haven't been able to find anything on the web about it yet. --
Dunx

 

[rt63]

select * from table
where ','+category+',' like '%,1,%'
RT

 

[dunxd]

Right! Nice piece of lateral thinking. And I can expand this to allow for the first and last in the field:

SELECT * FROM table WHERE (category + ',' LIKE '1,%') OR (',' + category + ',' LIKE '%,1,%') OR (',' + category LIKE '%,1)

Thanks.

Any idea how to get this working for selecting by multiple categories? Would it be easiest to create a series of three OR statements and put them in the SQL query, or is there a more simple way of doing this? --
Dunx

 

[rt63]

You need not change the code I had written for handling the first and the last value in the list. Just ','+category+',' LIKE '%,1,%' will work for all cases.

At present, I am not able to think of a simpler way for multiple values other tahn a series of ORs. Will let you know, if I can figure out an alternate method. RT

 

[dunxd]

Ok, I got you. It works because you put the commas around the whole thing. Thanks very much. That is exactly what I was looking for! --
Dunx

 

[dunxd]

Ok, I got you. It works because you put the commas around the whole category field. Thanks very much. That is exactly what I was looking for! --
Dunx

 

[MeanGreen]

RT,
You get a star from me for that solution. Very simple, but gets the job done.

Have a nice day!