Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test
    (
       start_range  VARCHAR2 (10),
       end_range    VARCHAR2 (10),
       val_range    NUMBER (10)
    );
 INSERT INTO test
        VALUES ('a1', 'a5', NULL);
1 rows affected

insert into test  values ('b1','b4', null)
1 rows affected

SELECT
    distinct 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_RANGE
        FROM
            TEST
    )
CONNECT BY
    LEVEL <= END_RANGE - START_RANGE + 1
    order by 1;


PREFIX||(START_RANGE+LEVEL-1)


a1

a2

a3

a4

a5

b1

b2

b3

b4