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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: