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?

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:
1 - 5
10 - 12

For More Information

This was last published in November 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: