Auto Incrementing Alphabet

[becky]

Hey there!

Can anyone this of an easy way in SQL to generate an auto-incrementing alphabetical series ?

A list like this:

• AA
  AB
  AC
  ...
  AZ
  BA
  BB
  BC
  ...
  BZ
  CA
  CB
  CC
  ...
  CZ
  ...
  ...
  ZA
  ZB
  ...
  ZZ

[id10t]

Hi!

I don't think it's possible to implement on SQL since it require holding previous state for generating current.

[JimKnicely]

Hey,

I wonder if something like this query would work?

Code: Select all

SELECT chr(foo.rn) || chr(foo2.rn) auto_alpha
  FROM (SELECT row_number() over () rn
          FROM tables) foo
 CROSS JOIN (SELECT row_number() over () rn
               FROM tables) foo2
 WHERE foo.rn BETWEEN 65 AND 90
   AND foo2.rn BETWEEN 65 AND 90
 ORDER
    BY chr(foo.rn) || chr(foo2.rn);

This is assuming the user has at least 26 records in the TABLES system table...

[becky]

Thanks! That does work

Sorry to bug you again, but what if I needed to changed the output to included A-Z at the beginning?

Something like:

• A
  B
  C
  ...
  Z
  AA
  AB
  AC
  ...
  BA
  BB
  BC
  ...
  ZZ

I'm trying to mimic the columns of an Excel spreadsheet.

Thanks for your assistance!

[id10t]

Hi!


Pure SQL function that maps decimal number to alpha base numbers, i.e 1 = A, 2 = B,...,26 = Z, 27, = AA

You have to use in function ROW_NUMBER() OVER () or sequences.

LIMITATIONS:
A <= row_number <= ZZ, i,e 1 <= rows <= 702 = 26^2 + 26
but it easy to extend, next upper bound is 17602 = 26^3 + 26

Code: Select all

CREATE OR REPLACE FUNCTION ALPHA_BASE(row_number INT) RETURN CHAR(3)
AS BEGIN
RETURN
 (
   CASE
      WHEN (row_number < 27)
          THEN CHR(row_number + 64)
      WHEN (row_number < 703)
          THEN CHR((row_number - 1) // 26 + 64) || CHR((row_number - 1) % 26 + 65)
   END
 );
END;

Usage:
SELECT alpha_base(row_number)...
For example:

Code: Select all

dbadmin=> select id, alpha_base(id), 26*7 + id, alpha_base(26*7 + id), 26*26 + id, alpha_base(26*26 + id) from nums limit 26;
 id | alpha_base | ?column? | alpha_base | ?column? | alpha_base 
----+------------+----------+------------+----------+------------
  1 | A          |      183 | GA         |      677 | ZA 
  2 | B          |      184 | GB         |      678 | ZB 
  3 | C          |      185 | GC         |      679 | ZC 
  4 | D          |      186 | GD         |      680 | ZD 
  5 | E          |      187 | GE         |      681 | ZE 
  6 | F          |      188 | GF         |      682 | ZF 
  7 | G          |      189 | GG         |      683 | ZG 
  8 | H          |      190 | GH         |      684 | ZH 
  9 | I          |      191 | GI         |      685 | ZI 
 10 | J          |      192 | GJ         |      686 | ZJ 
 11 | K          |      193 | GK         |      687 | ZK 
 12 | L          |      194 | GL         |      688 | ZL 
 13 | M          |      195 | GM         |      689 | ZM 
 14 | N          |      196 | GN         |      690 | ZN 
 15 | O          |      197 | GO         |      691 | ZO 
 16 | P          |      198 | GP         |      692 | ZP 
 17 | Q          |      199 | GQ         |      693 | ZQ 
 18 | R          |      200 | GR         |      694 | ZR 
 19 | S          |      201 | GS         |      695 | ZS 
 20 | T          |      202 | GT         |      696 | ZT 
 21 | U          |      203 | GU         |      697 | ZU 
 22 | V          |      204 | GV         |      698 | ZV 
 23 | W          |      205 | GW         |      699 | ZW 
 24 | X          |      206 | GX         |      700 | ZX 
 25 | Y          |      207 | GY         |      701 | ZY 
 26 | Z          |      208 | GZ         |      702 | ZZ 

Code: Select all

dbadmin=> select alpha_base(row_number() over ()), random() from nums;
 alpha_base |       random       
------------+--------------------
 A          |  0.622216116404161
 B          |  0.794488165294752
 C          |  0.159105612430722
 D          | 0.0314883585087955
 E          |  0.753018793649971
 F          |  0.631334234029055
...

[becky]

Thanks guys! As usual, somebody on this site always knows how to handle my oddball requests