comma seperated values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

comma seperated values

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-05 : 13:39:02

What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 13:48:19

You can serach in script library of this forum for a function called
dbo.fnParseList which parses a comma seperated list of value into an array of values and use it like this:-

SELECT *
FROM article a
INNER JOIN member m
ON m.articleid=a.articleid
WHERE a.articleid NOT IN (SELECT Data FROM dbo.fnParseList(',',@articleidlist))

Alternatively you can also do like this

SELECT *
FROM article a
INNER JOIN member m
ON m.articleid=a.articleid
WHERE ','+ @articleidlist + ',' NOT LIKE '%,'+ a.articleid + ',%'

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-05-05 : 14:34:22

Both do not seem to work. 2nd one gave me "Conversion failed when converting the varchar value '%,' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 14:39:36

SELECT *
FROM article a
INNER JOIN member m
ON m.articleid=a.articleid
WHERE ','+ @articleidlist + ',' NOT LIKE '%,'+ CAST(a.articleid as varchar(5))+ ',%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources