Convert a sequence of 0s and 1s to a print-style page list

Question

I need to convert a string of 0s and 1s into a sequence of integers representing the 1s, similar to a page selection sequence in a print dialog.

e.g. '0011001110101' -> '3-4,7-9,11,13'

Is it possible to do this in a single SQL select (in Oracle 11g)?

I can get an individual list of the page numbers with the following:

with data as (
    select 'K1' KEY,   '0011001110101' VAL from dual
    union select 'K2', '0101000110'   from dual
    union select 'K3', '011100011010' from dual
)
select
    KEY,
    listagg(ords.column_value, ',') within group (
        order by ords.column_value
    ) PAGES
from
    data
cross join (
    table(cast(multiset(
        select level
        from   dual
        connect by level <= length(VAL)
    ) as sys.OdciNumberList)) ords
)
where
    substr(VAL, ords.column_value, 1) = '1'
group by
    KEY

But that doesn't do the grouping (e.g. returns "3,4,7,8,9,11,13" for the first value).

If I could assign a group number every time the value changes then I could use analytic functions to get the min and max for each group. I.e. if I could generate the following then I'd be set:

Key     Page    Val     Group
K1      1       0       1
K1      2       0       1
K1      3       1       2
K1      4       1       2
K1      5       0       3
K1      6       0       3
K1      7       1       4
K1      8       1       4
K1      9       1       4
K1      10      0       5
K1      11      1       6
K1      12      0       7
K1      13      1       8

But I'm stuck on that.

Anyone have any ideas, or another approach to get this?

Answer 1

first of all let's level it:

select regexp_instr('0011001110101', '1+', 1, LEVEL) istr,
       regexp_substr('0011001110101', '1+', 1, LEVEL) strlen
FROM dual
CONNECT BY regexp_substr('0011001110101', '1+', 1, LEVEL) is not null

then the rest is easy with listagg :

with data as
 (
    select 'K1' KEY,   '0011001110101' VAL from dual
    union select 'K2', '0101000110'   from dual
    union select 'K3', '011100011010' from dual
 )
SELECT key,
       (SELECT listagg(CASE
                         WHEN length(regexp_substr(val, '1+', 1, LEVEL)) = 1 THEN
                          to_char(regexp_instr(val, '1+', 1, LEVEL))
                         ELSE
                          regexp_instr(val, '1+', 1, LEVEL) || '-' ||
                          to_char(regexp_instr(val, '1+', 1, LEVEL) +
                                  length(regexp_substr(val, '1+', 1, LEVEL)) - 1)
                       END,
                       ' ,') within GROUP(ORDER BY regexp_instr(val, '1+', 1, LEVEL))
          from dual
        CONNECT BY regexp_substr(data.val, '1+', 1, LEVEL) IS NOT NULL) val
  FROM data