How to group sequence numbers in oracle?

Question

I have a string which is years separated by comma.

For example 2000,2001,2002,2005,2006,2007 and 2010.

I want to group the consecutive numbers.

My output should be 2000-2003,2005-2007 and 2010. Is there any way to do this in Oracle Stored procedure?

Answer 1

Disclaimer - I don't recommend using this solution "as is", but it can give ideas, and it was fun writing it

I assume you have a column with the csv strings in a table.

If you're using oracle 11gR2 then you can use recursive CTEs-
Here is a sqlfiddle demo

with t as 
(
  select replace(replace(v, ' and ', ','), ' ','') v
  from strings
  ),
rcte(text, token, res) as
(
  select v, regexp_substr(v, '^\d*[^,]'), regexp_substr(v, '^\d*[^,]') ||'-'
  from t
  union all
  select regexp_replace(text, '^\d*,', ''), 
         regexp_substr(text, '^\d*[^,]'),
         case when regexp_substr(text, '^\d*[^,]') = token then
                   res
              when regexp_substr(text, '^\d*[^,]') = token+1 then  
                   regexp_replace(res, '-\d*$', '-'||(token+1))
              else rtrim(res, '-') || ',' || regexp_substr(text, '^\d*[^,]') || '-' 
         end
  from rcte
  where text <> token
  )
select rtrim(res, '-') from rcte
where text = regexp_substr(rtrim(res, '-'), '\d*$');

(This can be done without regular expressions as well)