Oracle-SQL: Generating cyclical, composite sequences

I want to generate composite sequences in the following format:

<Alphabet><2 digit numeric code>

Each alphabet series will have numeric values ranging from 00 to 99.

开发者_运维百科

The initial value will be A00, the subsequent values will be A01, A02 and so on. Upon reaching A99, the next sequence should carry-on to B00. When the "B" series is exhausted, it will move over to the C-series (i.e. C00) and so on. The sequence will continue until it reaches Z99 - at which point it will reset back to A00.

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

---

Personally I would store just a NUMBER and then calculate the "composite 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 assuming the English alphabet, modify for your desired alphabet

---

Use:

  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