SQL: Is there a better way?

[Goksly]

Hi,
I have a situation where some data is stored in a comma separated format. One cell in this table can mean many rows in another table (i.e. answers 'top', 'middle' and 'bottom' can all be represented by '1,1,1', where as 'top' and 'bottom' would be represented as '1,0,1').

Now, to count which options have been selected, I use a cursor to select one data cell (comma separated value) at a time and then process this; loop through the string checking for a '1' and then look up the full string answer in a table variable.

Now, looking at SQL profiling tool, this does seem to have a bit of a performance impact. I will be caching the results of this query, but still want to optimise queries at every possible opportunity. Unfortunatly I am unable to change how this data is stored...

So, is there a better way to take a comma separated representation of data and get the underlying data in a less expencive way?

Thanks for reading.

 

[Goksly]

Thanks for the feedback guys. At the moment I am unable to change a database structure that I have inherited - however, when time allows we will change it, which is another reason I want to use a stored proc / user defined function so we can change the underlying structure without affecting the web app.

cjm: I can't use regex as each value is a 1 or 0 and its location is the index of the look up table. In future, the CLR might be the way to go however!

sist_si: A look up table might be the answer, then (I assume) I can just do a join (on index) and filter on where value != 0. Hopefully that should get rid of the cursor, which is causing a fair bit of overhead atm.