question

Here is what I would do. Take the input string and split it into a proper data set - a column of data. Then use the 'islands' technique of 'Gaps and Islands' ( http://www.manning.com/nielsen/SampleChapter5.pdf) to determine the start and finish values of the island ranges. I've left my solution with the data as a result set - did you want to rejig this into a csv again? declare @input varchar(max) set @input = '1,2,3,6,7,8,10,11,12' set @input = ','+@input+',' --first split the string in to proper data, i.e. a column ;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) ,SplitandOrdered as ( select cast(substring(@input,N+1,charindex(',',@input,N+1)-N-1) as int) as Value from tally_cte where N < len(@input) and substring(@input,N,1) = ',' ) ,Islands as ( select min(Value) as start, max(Value) as finish from (select Value,Value - row_number()over (order by value) as grp from SplitandOrdered) X group by grp ) select cast(start as varchar) + '-' + cast(finish as varchar) from Islands This solution uses 2 ideas from [Itzik Ben-Gan][1] : one for a zero-IO tally table, and one for solving the 'Gaps and Islands' problem. [1]: https://twitter.com/itzikbengan