How to generate values between 2 values?-CodePudding

My data looks like below

with test(col) as (
    select  '01-06' from dual union all
    select  '45-52' from dual
    ) select col from test ;

Required OP

COL
01
02
.
.
.
06
45
46
.
.
52

Actually my table count is 20 thousand. I used connect by but its very slow.

CodePudding user response：

You can generate values using recursive query:

with test(col) as (
    select  '01-06' from dual union all
    select  '45-52' from dual
), bounds (l,u) as (
  select to_number(substr(col,1,2)), to_number(substr(col,4,2)) from test
), r (l,u) as (
  select l,u from bounds
  union all
  select r.l   1, r.u from r where r.l < r.u
)
select to_char(l,'00') from r order by l;

(edit substr expressions appropriately if any value is not 2-digit)

CodePudding user response：

Saying "I used connect by but its very slow" without posting code you wrote doesn't help much. I presume you did it wrong, i.e. got too many duplicate values which slowed things down. See if this connect by option helps.

SQL> with test (col)
  2  as
  3    (select '1-6' from dual
  4     union all
  5     select '45-52' from dual
  6    )
  7  select lpad(to_number(substr(col, 1, instr(col, '-') - 1))   column_value - 1, 2, '0') val
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level <= to_number(substr(col, instr(col, '-')   1)) -
 11                                            to_number(substr(col, 1, instr(col, '-') - 1))   1
 12                       ) as sys.odcinumberlist));

VAL
---
01
02
03
04
05
06
45
46
47
48
49
50
51
52

14 rows selected.

SQL>