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.

Answer 2

With this one you can generate sequences of any length:

SQL> with input as (
  2         select 0 as start_position, 3 as strings_length, 35 as sequence_elements, ascii('Z')-ascii('A')+1 as base, ascii('A') as start_val
  3         from dual
  4     )
  5  select sequence_position, listagg(character,'') within group(order by char_level desc) as sequence_value
  6  from (
  7         select inp_sequence.*, format.*, chr(start_val + mod(trunc(sequence_position / power(base, char_level)),base)) as character
  8         from (
  9                 select i.*, start_position + rownum-1 as sequence_position
 10                 from input i
 11                 connect by rownum <= sequence_elements
 12             ) inp_sequence
 13             cross join (
 14                 select rownum-1 as char_level
 15                 from input
 16                 connect by rownum <= strings_length
 17             ) format
 18     )
 19  group by sequence_position
 20  /

SEQUENCE_POSITION SEQUENCE_VALUE
----------------- --------------------------------------------------------------------------------
                0 AAA
                1 AAB
                2 AAC
                3 AAD
                4 AAE
                5 AAF
                6 AAG
                7 AAH
                8 AAI
                9 AAJ
               10 AAK
               11 AAL
               12 AAM
               13 AAN
               14 AAO
               15 AAP
               16 AAQ
               17 AAR
               18 AAS
               19 AAT
               20 AAU
               21 AAV
               22 AAW
               23 AAX
               24 AAY
               25 AAZ
               26 ABA
               27 ABB
               28 ABC
               29 ABD
               30 ABE
               31 ABF
               32 ABG
               33 ABH
               34 ABI

35 rows selected

SQL> 

Here:

• start_position indicates the element you want to start with (0 stands for AA..A)
• strings_length indicates the length of the strings in the sequence
• sequence_elements indicates the number of elements to generate (following start_position)

Answer 3

Using the SQL MODEL clause to generate the row list:

SELECT s
FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26)
MODEL 
  DIMENSION BY (pos1 AS x, 'A' as y)
  MEASURES ('--' AS s)
  RULES UPSERT ALL ITERATE (26)
(
 s[any, chr(iteration_number + 65)] = (CV(x) || CV(y))
)
ORDER BY s;

Sample run:

09/12/2015 19:55:26:SQL>     SELECT s
  2      FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26)
  3      MODEL
  4        DIMENSION BY (pos1 AS x, 'A' as y)
  5        MEASURES ('--' AS s)
  6        RULES UPSERT ALL ITERATE (26)
  7      (
  8       s[any, chr(iteration_number + 65)] = (CV(x) || CV(y))
  9      )
 10      ORDER BY s;

S
--
AA
AB
AC
AD
AE
etc ...

Then we can run a simple aggregation with LISTAGG as in:

WITH t AS (
SELECT s
FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26)
MODEL 
  DIMENSION BY (pos1 AS x, 'A' as y)
  MEASURES ('--' AS s)
  RULES UPSERT ALL ITERATE (26)
(
 s[any, chr(iteration_number + 65)] = (CV(x) || CV(y))
)
ORDER BY s)
-- data generated, now aggregate
SELECT listagg(s, ', ') WITHIN GROUP (ORDER BY s) aggreg_list
  FROM t;

And the results:

09/12/2015 19:52:59:SQL> WITH t AS (
  2  SELECT s
  3  FROM (select chr(level + 65 - 1) pos1 from dual connect by level <= 26)
  4  MODEL
  5    DIMENSION BY (pos1 AS x, 'A' as y)
  6    MEASURES ('--' AS s)
  7    RULES UPSERT ALL ITERATE (26)
  8  (
  9   s[any, chr(iteration_number + 65)] = (CV(x) || CV(y))
 10  )
 11  ORDER BY s)
 12  -- data generated, now aggregate
 13  SELECT listagg(s, ', ') WITHIN GROUP (ORDER BY s) aggreg_list
 14    FROM t;

AGGREG_LIST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ, AR, AS, AT, AU, AV, AW, AX, AY, AZ, BA, BB, BC, BD, BE, BF, BG, BH, BI, BJ, BK, BL, BM, BN, BO, BP, BQ, BR, BS, BT, BU, BV, BW, BX, BY, BZ, CA, CB, CC, CD, CE, CF, CG, CH, CI, CJ, CK, CL, CM, CN, CO, CP, CQ, CR, CS, CT, CU, CV, CW, CX, CY, CZ, DA, DB, DC, DD, DE, DF, DG, DH, DI, DJ, DK, DL, DM, DN, DO, DP, DQ, DR, DS, DT, DU, DV, DW, DX, DY, DZ, EA, EB, EC, ED, EE, EF, EG, EH, EI, EJ, EK, EL, EM, EN, EO, EP, EQ, ER, ES, ET, EU,
EV, EW, EX, EY, EZ, FA, FB, FC, FD, FE, FF, FG, FH, FI, FJ, FK, FL, FM, FN, FO, FP, FQ, FR, FS, FT, FU, FV, FW, FX, FY, FZ, GA, GB, GC, GD, GE, GF, GG, GH, GI, GJ, GK, GL, GM, GN, GO, GP, GQ, GR, GS, GT, GU, GV, GW, GX, GY, GZ, HA, HB, HC, HD, HE, HF, HG, HH, HI, HJ, HK, HL, HM, HN, HO, HP, HQ, HR, HS, HT, HU, HV, HW, HX, HY, HZ, IA, IB, IC, ID, IE, IF, IG, IH, II, IJ, IK, IL, IM, IN, IO, IP, IQ, IR, IS, IT, IU, IV, IW, IX, IY, IZ, JA, JB, JC, JD, JE, JF, JG, JH, JI, JJ, JK, JL, JM, JN, JO, JP,
JQ, JR, JS, JT, JU, JV, JW, JX, JY, JZ, KA, KB, KC, KD, KE, KF, KG, KH, KI, KJ, KK, KL, KM, KN, KO, KP, KQ, KR, KS, KT, KU, KV, KW, KX, KY, KZ, LA, LB, LC, LD, LE, LF, LG, LH, LI, LJ, LK, LL, LM, LN, LO, LP, LQ, LR, LS, LT, LU, LV, LW, LX, LY, LZ, MA, MB, MC, MD, ME, MF, MG, MH, MI, MJ, MK, ML, MM, MN, MO, MP, MQ, MR, MS, MT, MU, MV, MW, MX, MY, MZ, NA, NB, NC, ND, NE, NF, NG, NH, NI, NJ, NK, NL, NM, NN, NO, NP, NQ, NR, NS, NT, NU, NV, NW, NX, NY, NZ, OA, OB, OC, OD, OE, OF, OG, OH, OI, OJ, OK,
OL, OM, ON, OO, OP, OQ, OR, OS, OT, OU, OV, OW, OX, OY, OZ, PA, PB, PC, PD, PE, PF, PG, PH, PI, PJ, PK, PL, PM, PN, PO, PP, PQ, PR, PS, PT, PU, PV, PW, PX, PY, PZ, QA, QB, QC, QD, QE, QF, QG, QH, QI, QJ, QK, QL, QM, QN, QO, QP, QQ, QR, QS, QT, QU, QV, QW, QX, QY, QZ, RA, RB, RC, RD, RE, RF, RG, RH, RI, RJ, RK, RL, RM, RN, RO, RP, RQ, RR, RS, RT, RU, RV, RW, RX, RY, RZ, SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL, SM, SN, SO, SP, SQ, SR, SS, ST, SU, SV, SW, SX, SY, SZ, TA, TB, TC, TD, TE, TF,
TG, TH, TI, TJ, TK, TL, TM, TN, TO, TP, TQ, TR, TS, TT, TU, TV, TW, TX, TY, TZ, UA, UB, UC, UD, UE, UF, UG, UH, UI, UJ, UK, UL, UM, UN, UO, UP, UQ, UR, US, UT, UU, UV, UW, UX, UY, UZ, VA, VB, VC, VD, VE, VF, VG, VH, VI, VJ, VK, VL, VM, VN, VO, VP, VQ, VR, VS, VT, VU, VV, VW, VX, VY, VZ, WA, WB, WC, WD, WE, WF, WG, WH, WI, WJ, WK, WL, WM, WN, WO, WP, WQ, WR, WS, WT, WU, WV, WW, WX, WY, WZ, XA, XB, XC, XD, XE, XF, XG, XH, XI, XJ, XK, XL, XM, XN, XO, XP, XQ, XR, XS, XT, XU, XV, XW, XX, XY, XZ, YA,
YB, YC, YD, YE, YF, YG, YH, YI, YJ, YK, YL, YM, YN, YO, YP, YQ, YR, YS, YT, YU, YV, YW, YX, YY, YZ, ZA, ZB, ZC, ZD, ZE, ZF, ZG, ZH, ZI, ZJ, ZK, ZL, ZM, ZN, ZO, ZP, ZQ, ZR, ZS, ZT, ZU, ZV, ZW, ZX, ZY, ZZ