问Oracle :将连字符分隔的数值转换为逗号分隔的范围

EN

with str as
 (select '1,2,3,7-11' as ids from dual),
i as
 (select replace(trim(substr(t1.str,
                             t1.curr_pos + 1,
                             decode(t1.next_pos, 0, length(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1)),
                 ' ',
                 '') as item
    from (select ids str,
                 decode(level, 1, 0, instr(ids, ',', 1, level - 1)) as curr_pos,
                 instr(ids, ',', 1, level) as next_pos
            from str
          connect by level <= length(ids) - length(replace(ids, ',', '')) + 1) t1),
ii as
 (select case
             when instr(item, '-') = 0 then
              item
             else
              substr(item, 1, instr(item, '-') - 1)
         end as first_item,
         case
             when instr(item, '-') = 0 then
              item
             else
              substr(item, instr(item, '-') + 1)
         end as last_item
    from i),
tmp_str as
 (select rownum as id,
         (select listagg(ii.first_item + level - 1, ',') within group(order by level)
            from dual
          connect by level <= ii.last_item - ii.first_item + 1) as ids
    from ii),
tmp_str2 as
 (select t.*, length(ids) - length(replace(ids, ',', '')) + 1 as cnt from tmp_str t),
tmp_dual as
 (select level as lv from dual connect by level <= (select max(cnt) as total_cnt from tmp_str2)),
tmp_str3 as
 (select *
    from (select id, lv, ids, cnt, row_number() over(partition by id order by lv) as rn from tmp_dual, tmp_str2) t1
   where rn <= cnt)
select distinct to_number(trim(substr(t1.str,
                                      t1.curr_pos + 1,
                                      decode(t1.next_pos, 0, length(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1))) as id
  from (select ids str, decode(lv, 1, 0, instr(ids, ',', 1, lv - 1)) as curr_pos, instr(ids, ',', 1, lv) as next_pos
          from tmp_str3) t1
 order by 1

1
2
3
7
8
9
10
11