How to generate alphabetic letters between 2 values in oracle sql?-CodePudding

select 'V4A-V4G' FROM DUAL;

sample output:

    V4A
    V4B
    V4C
    V4D
    V4E
    V4G

select 'R8M-R8S' FROM DUAL;

sample output:

    R8M
    R8N
    R8O
    R8P
    R8Q
    R8R
    R8S

CodePudding user response：

Here's one option: for sample data you posted (I put them into the same test CTE), find ASCII codes of the 3rd and the last letter (as you said - in a comment - that they are used only) and do a little bit of a row-generator calculation.

SQL> with test (id, col) as
  2    (select 1, 'V4A-V4G' from dual union all
  3     select 2, 'R8M-R8S' from dual
  4    )
  5  select id,
  6    substr(col, 1, 2) || chr(ascii(substr(col, 3, 1))   column_value - 1) val
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= ascii(substr(col, -1)) - ascii(substr(col, 3, 1))   1
 10                       ) as sys.odcinumberlist))
 11  order by id, val;

        ID VAL
---------- ---
         1 V4A
         1 V4B
         1 V4C
         1 V4D
         1 V4E
         1 V4F
         1 V4G
         2 R8M
         2 R8N
         2 R8O
         2 R8P
         2 R8Q
         2 R8R
         2 R8S

14 rows selected.

SQL>

CodePudding user response：

A more direct way (avoiding the CONNECT BY process):

with
  test (id, col) as (
    select 1, 'V4A-V4G' from dual union all
    select 2, 'R8M-R8S' from dual
  )
select id, substr(col, 1, 2) || column_value as val
from   test join sys.odcivarchar2list('A','B','C','D','E','F','G','H','I',
       'J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
       on column_value between substr(col, 3, 1) and substr(col, 7, 1)
order  by id, val  --  if needed
;

Of course, if you need to do this often, you can use an actual table with one column and 26 rows holding the capital letters, so you won't need to create it on the fly in every query that uses it (and every time the query is used). That would make the query that much simpler still.

Note - in older Oracle versions you may need to wrap sys.odci...list within table( ... ).