Oracle-SQL: looping, compound sequences

Question

I want to generate compound sequences in the following format:

<Alphabet><2 digit numeric code>

Each series of alphabets will have numerical values ​​from 00 to 99.

The initial value will be A00 , subsequent values ​​will be A01 , A02 and so on. Upon reaching A99 , the following sequence should continue to B00 . When the “B” series is exhausted, it will move on to the C-series (that is, C00 ) and so on. The sequence will continue until it reaches Z99 - at that moment it will return reset to A00 .

How can this be done in SQL (or PL / SQL)?

Answer 1

Personally, I would save only NUMBER, and then compute the "compound sequence" on the fly with something like:

select
chr (ascii ('A') + ((number_sequence div 100) mod 26)) || to_char (number_sequence mod 100) composite_sequence,
...
from mytable

26 subject to the English alphabet, change for the desired alphabet

Answer 2

Using:

  SELECT CHR(x.ascii) || LPAD(y.num - 1, 2, '0') AS val
    FROM (SELECT 64 + LEVEL AS ascii
          FROM DUAL
    CONNECT BY LEVEL <= 26) x,
      (SELECT LEVEL AS num
         FROM DUAL
   CONNECT BY LEVEL <= 100) y