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.