Select from comma delimited list in MSSQL

Hi There,

I have a field containing a comma delimited list of numbers associating the records to n-categories and want to return all the records in a given category (there are reasons why I'm not breaking this out into a joining table).

So, a given item may have a CategoryList field of 1,7,12,41 and I want SQL to return all items where X (e.g. 1) is in the category list. Of course, if an item has a CategoryList of 12,13 it should NOT show up in categories 1,2 or 3 - only in 12 and 13.

Right now I have the following SQL:
select * from SFC_DataCard
where DataCardCategory LIKE '#attributes.CatID#'
or DataCardCategory LIKE '%,#attributes.CatID#'
or DataCardCategory LIKE '#attributes.CatID#,%'
or DataCardCategory LIKE '%,#attributes.CatID#,%'

This works fine, but must be even more horrible than necessary from a processing perspective.

Was looking for a simple clean and efficient (performance enhancing) way to pull a value from a comma delimited list in a select statement. Key is that performance must be better than the above solution.

Any help much appreciated.

Best Wishes,
Peter
freshstartusaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

illCommented:
select * from SFC_DataCard
where  ',' +DataCardCategory+',' like   ('%,'+cast( attributes.CatID as varchar(8000) )+ ',%' )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rafranciscoCommented:
In terms of simple and clean, you can try this:

select * from SFC_DataCard
where ',' + DataCardCategory + ',' LIKE '%,#attributes.CatID#,%'

Regardless which option you choose, it will still perform a full table scan.
freshstartusaAuthor Commented:
Hi Guys,

Appreciate comments, but don't see how they would work. They assume the category is always prepended and followed by a comma.

Some possible cases:
CategoryList = 12
CategoryList = 11,12
CategoryList = 12,13
CategoryList = 11,12,13

So the SQL needs to pick up:
LIKE '12'
LIKE '%,12'
LIKE '12,%'
and LIKE '%,12,%' to cover all of the bases which is the code I already have.

What I'm looking for is something like a ListFind function that finds a number within a comma delimited list without falsely picking out partial numbers (so 14,123,411 wouldn't come up as category 12 by reading the partial of 123).

Any input much appreciated!

Best Wishes,
Peter



freshstartusaAuthor Commented:
Ohhhh, sorry - just re-read posts more carefully. You are right. Ignore me. Points awarded to first response. Many thanks for your input.

Best Wishes,
Peter
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.