Re: T-SQL Help: Matching a Comma Separated List

[Plamen Ratchev]

There should be no need to split the list, you can use LIKE to match the
specific department, see the example below:

CREATE TABLE Members (
keycol INT PRIMARY KEY,
depts VARCHAR(30))

INSERT INTO Members VALUES (1, '1,22,5')
INSERT INTO Members VALUES (2, '5,8,1')
INSERT INTO Members VALUES (3, '9,5,2')
INSERT INTO Members VALUES (4, '3,4,6')

DECLARE @dept2find CHAR(1)

SET @dept2find = '5'

SELECT keycol, depts
FROM Members
WHERE ',' + depts + ',' LIKE '%,' + @dept2find + ',%'

But on the long run normalizing the table will make it much easier to query
and maintain.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Anith Sen]

>> I have a table named 'Members' containing a column named 'DeptIDs'.
>> DeptIDs contains a comma-separated list of values.

It is not a recommended way to represent values in a table -- one value per
column is one of the fundamentals of SQL and relational databases.
Mulitvalued approaches are usually the best way to muliply maintenance
headaches. If for some reason the schema design is beyond your control and
you are looking for a quick short term solution, consider Plamen's method or
any one that detailed at: http://www.projectdmx.com/tsql/sqlarrays.aspx

--
Anith

[Mike]

One thousand THANK YOUs for your syntax help! Yes I know I must ultimately
normalize the table for maximum performance. Thanks again! Mike