Range Fill Table

Good day! There is a table: CREATE TABLE table ( start_range varcahar2(10), end_range varcahar2(10), val_range NUMBER(10) ); At the initial stage, we filled in two fields: start_

Solution 1:

You can use the following:

SELECTDISTINCT PREFIX || ( START_RANGE + LEVEL - 1 )
FROM
    (
        SELECT
            REGEXP_SUBSTR(START_RANGE, '^[[:alpha:]]+') AS PREFIX,
            REGEXP_SUBSTR(START_RANGE, '\d+$') AS START_RANGE,
            REGEXP_SUBSTR(END_RANGE, '\d+$') AS END_RANGEFROM
            TEST
    )
CONNECT BY
    LEVEL <= END_RANGE - START_RANGE + 1ORDERBY1;

I am assuming that your start and end range have same prefix with format(string||number)

db<>fiddle demo

Cheers!!

Solution 2:

Looks like a hierarchical query.

Test case:

SQL>CREATETABLE test
  2  (
  3     start_range  VARCHAR2 (10),
  4     end_range    VARCHAR2 (10),
  5     val_range    NUMBER (10)
  6  );

Table created.

SQL>INSERTINTO test
  2VALUES ('a1', 'a5', NULL);

1row created.

SQL>INSERTINTO TEST
  2VALUES ('L4819201', 'L4819205', NULL);

1row created.

SQL>SELECT*FROM test;

START_RANG END_RANGE   VAL_RANGE
---------- ---------- ----------
a1         a5
L4819201   L4819205

Query:

SQL>INSERTINTO test2 (val)
  2SELECT    SUBSTR (start_range, 1, 1)
  3|| TO_CHAR (
  4                  (  TO_NUMBER (REGEXP_SUBSTR (start_range, '\d+$'))
  5+ COLUMN_VALUE
  6-1))
  7AS val
  8FROM test
  9CROSSJOIN10TABLE (
 11CAST (
 12MULTISET (
 13SELECT LEVEL
 14FROM DUAL
 15CONNECTBY LEVEL <=16                                     TO_NUMBER (
 17                                        REGEXP_SUBSTR (end_range, '\d+$'))
 18- TO_NUMBER (
 19                                        REGEXP_SUBSTR (start_range, '\d+$'))
 20+1) AS SYS.odcinumberlist))
 21WHERE start_range ='&start_range';
Enter valuefor start_range: a1

5rows created.

SQL>/
Enter valuefor start_range: L4819201

5rows created.

Result:

SQL>SELECT*FROM test2 ORDERBY val;

VAL
----------
a1
a2
a3
a4
a5
L4819201
L4819202
L4819203
L4819204
L4819205

10rows selected.

SQL>