question

taj avatar image
taj asked

number sequence as per requirement

i/p : '1,2,3,6,7,8,10,11,12' o/p: '1-3,6-8,10-12' here when we give input like, comma separated numbers of different series then output should be like each series with underscore and separated by comma
sql-server-2008homework
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
given the proliferation of similar questions today, I've tagged as homework
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.