Problem solve Get help with specific problems with your technologies, process and projects.

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

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.