Ask the Expert

Representing set of numbers as set of ranges

I need to be able to insert a comma or a dash between two numbers, depending on if the numbers are consecutive or not. For example, Numbers 1,2,3,4,5,7,10,11,12 need to be shown as 1-5,7,10-12 (the numbers will be parsed in). Are you able to help?

    Requires Free Membership to View

    Login

My solution to this problem may be a little difficult to follow, so I'll try to explain. My approach here was to return a set of ranges. The WHERE clause eliminates all of the numbers but those that would be the first in a range, our range "starters". However, the case statement here does all the real work. The WHEN clause checks to see if there exists a number right after our starter. If so, then we know it is truly a range, and not just a single number by itself, like the number 7 in the example. In this case, we return the starter number, concatenated with a dash to indicate a sequence of numbers, and then concatenated with a sub-query. This sub-query returns the lowest number in our set greater than our starter, that does not have a number immediately after it, our "finisher". Our stand-alone numbers, like 7, are dealt with in the CASE's ELSE clause, and simply displayed. 

select case 
         when exists (select * from nums t where t.num = n.num + 1)
         then cast (num as varchar(10)) || ' - ' || 
              ( select min(t1.num) from nums t1 
                  where t1.num > n.num 
                    and not exists ( select * from nums t2 
                                     where t2.num = t1.num + 1 
                                   )
              )
         else cast (num as varchar(10)) 
       end as "Ranges"
  from nums n
  where not exists (select * from nums t3 where t3.num = n.num - 1)
  order by n.num
Here are the resulting rows: 
Ranges
-------
1 - 5
7
10 - 12

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top