Convert Comma Separated List of INTs to a Range 2

[rharsh]

I have a series of string values that are generally a contiguous list of integers in a format of "1,2,3,4,5," -- although sometimes there can be contiguous ranges mixed with non-contiguous entries, such as "0,1,2,3,10,13,20,21,22,".

What I'm hoping to do is convert them to a range notation. So

"1,2,3,4,5," becomes "1-5" and
"0,1,2,3,10,13,20,21,22," becomes "0-3,10,13,20-22"

I've implemented fixes in both perl and Crystal Reports formulas. Googling hasn't seemed to produce much help, but maybe I'm searching for the wrong thing. I was hoping someone might be able to point me in the right direction so I could implement this with a user defined function. Thanks!

 

[TamarGranor]

Are you trying to take the existing list and convert it to range form, or are you trying to take a list that includes ranges and use it in some way?

Tamar

 

[rharsh]

Thank you for the response, the field has values that are a list, some examples would be:

'1,'
'1,2,3,4,5,'
'1,2,3,5,6,10,'

And I'd like to convert it to a range (or ranges) if possible. Using those examples, something like:

'1'
'1-5'
'1-3,5-6,10'

I've continued searching and it seems the reverse is fairly easy (from a range to a list) but I'm not even sure where to start with identifying the ranges and recursing through the elements in the list.

 

[fredericofonseca]

yeah... searching for the right thing is most often where it fails.

This is a classic Gap and Islands case.

Code below is based on Jeff Moden's example at

https://www.sqlservercentral.com/articles/group-islands-of-contiguous-dates-sql-spackle

you will also need the split function found at

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

or the enhanced version if your sql version allows the LEAD() function at

https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2

Below two blocks of code - first one just to show how it works, the second one would be the one you would stick on a function to return the string as you asked for.
It is advisable that you do the function as an ITVF for performance reasons

declare @Input varchar(200) = '0,1,2,3,10,13,20,21,22,';

with sequences
as
(select convert(int, t.Item) as SeqNo
       from dbo.DelimitedSplit8K(@Input, ',') t
       where t.Item <> ''
),
groupedseqno
as
( -- Find the unique sequences and assign them to a group.
   -- adjacent Sequences will be a part of the same group.
   select UniqueSeqNo = SeqNo
        , SequenceGroup = SeqNo - row_number() over (order by SeqNo)
       from sequences
       group by SeqNo
)
-- Now, if we find the MIN and MAX sequence for each Group, we'll have the
-- Start and End sequence of each group of contiguous sequence.  While we're at it,
-- we can also figure out how many sequences are in each range of sequences.
select SequenceGroup
     , startseqno = min(UniqueSeqNo)
     , endseqno = max(UniqueSeqNo)
     , rangeentries = max(UniqueSeqNo) - min(UniqueSeqNo) + 1
from groupedseqno
group by SequenceGroup
order by startseqno;

--output
SequenceGroup startseqno endseqno rangeentries
-1            0          3        4
5             10         10       1
7             13         13       1
13            20         22       3

declare @Input varchar(200) = '0,1,2,3,10,13,20,21,22,';
with sequences
as
(select convert(int, t.Item) as SeqNo
       from dbo.DelimitedSplit8K(@Input, ',') t
       where t.Item <> ''
),
groupedseqno
as
( -- Find the unique sequences and assign them to a group.
   -- adjacent Sequences will be a part of the same group.
   select UniqueSeqNo = SeqNo
        , SequenceGroup = SeqNo - row_number() over (order by SeqNo)
       from sequences
       group by SeqNo
)
-- Now, if we find the MIN and MAX sequence for each Group, we'll have the
-- Start and End sequence of each group of contiguous sequence.  While we're at it,
-- we can also figure out how many sequences are in each range of sequences.
select stuff(
           (select ',' +
                        case
                        when min(UniqueSeqNo) = max(UniqueSeqNo)
                            then convert(varchar(10), max(UniqueSeqNo))
                        else convert(varchar(10), min(UniqueSeqNo)) + '-' + convert(varchar(10), max(UniqueSeqNo))
                        end

                  from groupedseqno
                  group by SequenceGroup
                  for xml path ('')
           ), 1, 1, ''
            ); 
-- output
0-3,10,13,20-22

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[rharsh]

Wow, thank you! Lots to read through there. Thank you for the links and the sample code!