Query (count) question

I am somewhat new to SQL and I have a simple question (I think)... I have a field called results that contains several numbers seperated by columns, such as 3,6,16,22 etc.

I want to write a query that answers how many of each number occurs from the range.

Example:

Select (total) results where id = 6 and results = 16

so the query would have to search within the string results for all the records retrieved and count the instances

how would I do this?

I'd do the following. First you'll need a numbers table as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

now create a view on your table that splits out the numbers

create view SplitResults
as
select id,
cast(substring(results,
Number,
charindex(',',
results + ',',
Number) - Number) as int) as results
from MyTable
inner join Numbers on Number between 1 and len(results) + 1
and substring(',' + results, Number, 1) = ','
GO

Finally, run a query on the view to get your results

select count(*)
from SplitResults
where id=6 and results=16

|||Thanks, I new it was simple