Using a table of numbers as in

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

DECLARE @STR VARCHAR(100)

SET @STR='1, 2, 3, 4, 5, 50, 99 ,100'

SELECT CAST(SUBSTRING(@str,

Number,

CHARINDEX(',',

@STR+',',

Number)-Number) AS INT) AS Val

FROM dbo.Numbers

WHERE Number BETWEEN 1 AND LEN(@str)+1

AND SUBSTRING(','+@str,Number,1)=','

This can also be easily be done with a CLR (using a the C# 'Split' function)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537