Generating an alphabetic sequence in oracle

Question

I'm looking for a way of generating an alphabetic sequence:

AA, AB, AC, AD, AE, ..., ZZ.

Can anyone suggest a convenient way of doing this in oracle.

Answer 1

This seems to work if you want it done in pure SQL:

SELECT CHR(divided+65)||CHR(remainder+65)
  FROM (
  SELECT 0 AS initial_val,
         0 AS divided,
         0 AS remainder
    FROM dual
 UNION
 SELECT LEVEL AS initial_val,
        TRUNC(LEVEL/26) AS divided,
        MOD(LEVEL,26) AS remainder
   FROM dual
CONNECT BY LEVEL <= 675)
ORDER BY initial_val

It builds a list of 676 rows (had to use union as the first record in the hierarchical query starts at 1 rather than 0).

676 is 26 x 26 so it then divides the number to get a number for the leading letter (converted to a letter in the main SELECT) and uses the remainder of that division for the trailing letter.

I then add 65 to get the number up into the ASCII region for alphabet letters and there you go.