I need to be able to insert a comma or a dash between two numbers, depending on if the numbers are consecutive...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
Ranges ------- 1 - 5 7 10 - 12
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.