Generating an alphabetic sequence with number in oracle

Question

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

A1,A2,A3,A4,A5,A6,A7,A8,A9 , B1,B2,B3....., Z9.

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

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

This sql is giving the output

AA to AZ, BA to ZZ

Answer 1

You can use ROW_NUMBER() analytic function:

Select Chr(divided + 65) || ROW_NUMBER() OVER(Partition By CHR(divided+65) Order By CHR(divided+65)) "COL"
From (  Select 0 AS initial_val, 0 AS divided
        From dual 
      UNION 
        Select LEVEL AS initial_val, TRUNC(LEVEL/9) AS divided
        From dual 
        CONNECT BY LEVEL <= 233
     ) 

/*   R e s u l t :
COL    
-------
A1
A2
A3
A4
...
...
B1
B2
B3
...
...
Z6
Z7
Z8
Z9      */

Answer 2

Here's one option, using two CTEs - one for letters (A - Z), and another one for digits (1 - 9). Cross join them for the final result.

SQL> with
  2  let as (select level l_let from dual
  3          connect by level <= ascii('Z') - ascii ('A') + 1
  4         ),
  5  dig as (select level l_dig from dual
  6          connect by level <= 9
  7         )
  8  select chr(65 + l_let - 1) || l_dig as value
  9  from let cross join dig;

VALUE
-----------------------------------------
A1
A2
A3
A4
A5
A6
A7
A8
A9
B1
B2
B3
B4
B5
B6
B7
B8
B9
C1
<snip>
Y4
Y5
Y6
Y7
Y8
Y9
Z1
Z2
Z3
Z4
Z5
Z6
Z7
Z8
Z9

234 rows selected.

SQL>

Answer 3

You can use:

SELECT CHR(65 + TRUNC((LEVEL - 1) / 9)) || (MOD(LEVEL - 1, 9) + 1) AS seq
FROM   DUAL
CONNECT BY LEVEL <= 26 * 9

Which outputs:

SEQ
A1
A2
A3
A4
A5
A6
A7
A8
A9
B1
B2
...
Z8
Z9

fiddle