Skip to Main Content
  • Questions

  • Generating alphabetical sequence like a spreadsheet

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, VA.

Asked: November 22, 2011 - 2:18 am UTC

Last updated: June 02, 2023 - 12:53 am UTC

Version: 11.1

Viewed 10K+ times! This question is

You Asked

Is there a way to generate an alphabetical iterator like the column headings in a spreadsheet? i.e. A...Z and then AA,AB,AC,...,AZ,BA,BB,BC,...,BZ and so on.

Googling leads to http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/73630/Generating-alphabetical-sequences-A-B-C-AA-AB-AC but I am afraid I don't quite undersstand Joe Celko's brilliant solution.

Help? Thanks

and Tom said...

ops$tkyte%ORA11GR2> with
  2  letters
  3  as
  4  (select chr( ascii('A')+level-1 ) letter
  5     from dual
  6  connect by level <= 26
  7  )
  8  select *
  9    from letters
 10   union all
 11  select l1.letter || l2.letter
 12    from letters l1, letters l2
 13   union all
 14  select l1.letter || l2.letter || l3.letter
 15    from letters l1, letters l2, letters l3
 16  /

LET
---
A
B
C
...
Z
AA
AB
...
AY
AZ
BA
BB
...
ZZ
AAA
AAB
...
ZZZ

18278 rows selected.



is one approach.


this is another that can generate as many as you want - you'd need to add more coolumns in addition to i1, i2, i3 of course, but that is rather easy:

ops$tkyte%ORA11GR2> select i1, i2, i3, l,
  2             case when i3 > 0 then chr(i3+ascii('A')-1) end ||
  3             case when i2 > 0 then chr(i2+ascii('A')-1) end ||
  4         chr(i1+ascii('A')-1)
  5             str
  6    from (
  7  select mod((level-1),27) i1,
  8         mod( trunc((level-0.1)/27), 27) i2,
  9             mod( trunc((level-0.1)/27/27), 27 ) i3,
 10             level l
 11    from dual
 12  connect by level <= 18200
 13         )
 14   where i1 <> 0
 15     and NOT( l>= 27*27 and i2 = 0)
 16     and NOT( l>= 27*27*27 and i3 = 0)
 17  /

        I1         I2         I3          L STR
---------- ---------- ---------- ---------- ---
         1          0          0          2 A
         2          0          0          3 B
         ...
        26          0          0         27 Z
         1          1          0         29 AA
         2          1          0         30 AB
         3          1          0         31 AC
         ...
        26          1          0         54 AZ
         1          2          0         56 BA
        ...
        25         26          0        728 ZY
        26         26          0        729 ZZ
         1          1          1        758 AAA
         2          1          1        759 AAB
         3          1          1        760 AAC
        ...
        25         25         24      18197 XYY
        26         25         24      18198 XYZ
         1         26         24      18200 XZA

16901 rows selected.


Joe's approach is using some SQL I've not seen nor used personally.

Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

VA NJ, November 23, 2011 - 6:15 am UTC

Thanks. I am using this in an APEX app to map collection columns (c001..c050) to a spreadsheet upload (A..Z) and I came up with the following before your response. It is similar to your first approach.

Your second approach sailed over my head, didn't understand the 0.1 part, maybe you can explain a bit when you get the chance.

As always, thanks a lot, don't know how you do it.

with alpha as(
select
 chr(level+65-1) letter
from dual
connect by level<=26
),
xls1 as ( 
select
a.letter||b.letter alpha
from alpha a,alpha b
),
letters as (
select
 xls1.alpha
from xls1
union
select
 letter
from alpha
),
output as ( 
select 
 row_number() over (order by length(alpha),alpha) num,
 alpha  
from letters l 
)
select * from  output
where rownum<=50;

Tom Kyte
November 23, 2011 - 8:43 am UTC

my second approach is a simple mapping function.


Take a single monotonically increasing set of numbers - 1, 2, 3, 4, ....

Now you know there must be some mapping of that set of numbers to A, B, C, ..., AA, AB, AC, ... and so on. They go hand in hand.

So, we use mod and integer division to get a sequence

ops$tkyte%ORA11GR2> select mod((level-1),27) i1,
  2         mod( trunc((level-0.1)/27), 27) i2,
  3             mod( trunc((level-0.1)/27/27), 27 ) i3,
  4             level l
  5    from dual
  6  connect by level <= 18200
  7  /

        I1         I2         I3          L
---------- ---------- ---------- ----------
         0          0          0          1
         1          0          0          2
         2          0          0          3
...
        26          0          0         27
         0          1          0         28
         1          1          0         29
...
        26          1          0         54
         0          2          0         55
...
        26          2          0         81
         0          3          0         82
...
         0          0          1        730
         1          0          1        731
         2          0          1        732
...
        26         25         24      18198
         0         26         24      18199
         1         26         24      18200

18200 rows selected.



Now, I just pretended that 0 = "blank", 1 = A, 2 = B, and so on...


we then put them together - but only want to keep strings such that:

a) i1 is not blank ever, we don't want a string 
 ' '


b) the sequence is above 27^2 and i2 is 'blank', because we don't want strings
 'A A'


if the sequence is below 27^2, then having i2 'blank' is good because we DO want strings
 'A  '


c) similar logic for i3 as i2.


In hindsight, I could have used 27 for i2, and 27^2 for i3 but the result would have been the same.



Think in math sometimes :)


Thanks

VA NJ, November 23, 2011 - 11:05 am UTC

Thanks, superb explanation, makes you go Duh, of course!

Variations

Jichao Li, November 23, 2011 - 9:33 pm UTC

Tom,

Here is a variation that utilizes recursive query in 11R2. You may like it. :)

column S2 format a20;
column S8 format a10;
column S16 format a10;
column s26 format a10;
column s26x format a10;
WITH
lst AS
  (SELECT l FROM (SELECT level AS l FROM dual CONNECT BY level < 200020)
    WHERE (l between 1 and 3) OR (l between 26 and 29)
      OR  (l between 52 and 55) OR (l between 1000 and 1003)
      OR  (l between 10000 and 10003) OR (l between 200000 and 200003)),
numlist(l, f2, s2, f8, s8, f16, s16, f26, s26, f26x, s26x) AS
  (SELECT l,
          floor(l / 2) AS f2,
          rpad(chr(48 + mod(l, 2)), 100, ' ') AS s2,
          floor(l / 8) AS f8,
          rpad(chr(48 + mod(l, 8)), 100, ' ') AS s8,
          floor(l / 16) AS f16,
          case when mod(l, 16) between 0 and 9 then
            rpad(chr(48 + mod(l, 16)), 100, ' ')
          else
            rpad(chr(65 + mod(l, 16) - 10), 100, ' ')
          end AS s16,
          floor(l / 26) AS f26,
          case when mod(l, 26) between 0 and 9 then
            rpad(chr(48 + mod(l, 26)), 100, ' ')
          else
            rpad(chr(65 + mod(l, 26) - 10), 100, ' ')
          end AS s26,
          floor((l - 1) / 26) AS f26x,
          rpad(chr(64 + mod(l - 1, 26) + 1), 100, ' ') AS s26x
     FROM lst
    UNION ALL
   SELECT l,
          DECODE(f2, 0, 0, floor(f2 / 2)),
          DECODE(f2, 0, s2, chr(48 + mod(f2, 2)) || trim(s2)),
          DECODE(f8, 0, 0, floor(f8 / 8)),
          DECODE(f8, 0, s8, chr(48 + mod(f8, 8)) || trim(s8)),
          DECODE(f16, 0, 0, floor(f16 / 16)),
          DECODE(f16, 0, s16, case when mod(f16, 16) between 0 and 9 then
                                chr(48 + mod(f16, 16))
                              else
                                chr(65 + mod(f16, 16) - 10)
                              end || trim(s16)),
          DECODE(f26, 0, 0, floor(f26 / 26)),
          DECODE(f26, 0, s26, case when mod(f26, 26) between 0 and 9 then
                                chr(48 + mod(f26, 26))
                              else
                                chr(65 + mod(f26, 26) - 10)
                              end || trim(s26)),
          DECODE(f26x, 0, 0, floor((f26x - 1) / 26)),
          DECODE(f26x, 0, s26x, chr(64 + mod(f26x - 1, 26) + 1) || trim(s26x))
     FROM numlist
    WHERE f26x ! = 0 or f2 != 0 or f8 != 0 or f16 != 0 or f26 != 0)
 SELECT l, trim(s2) as s2, trim(s8) as s8, 
           trim(s16) as s16, trim(s26) as s26, 
           trim(s26x) AS s26x 
   FROM numlist
  WHERE f26x = 0
    AND f2 = 0
    AND f8 = 0
    AND f16 = 0
    AND f26 = 0;

         L S2                   S8         S16        S26        S26X
---------- -------------------- ---------- ---------- ---------- ----------
         1 1                    1          1          1          A
         2 10                   2          2          2          B
         3 11                   3          3          3          C
        26 11010                32         1A         10         Z
        27 11011                33         1B         11         AA
        28 11100                34         1C         12         AB
        29 11101                35         1D         13         AC
        52 110100               64         34         20         AZ
        53 110101               65         35         21         BA
        54 110110               66         36         22         BB
        55 110111               67         37         23         BC
      1000 1111101000           1750       3E8        1CC        ALL
      1001 1111101001           1751       3E9        1CD        ALM
      1002 1111101010           1752       3EA        1CE        ALN
      1003 1111101011           1753       3EB        1CF        ALO
     10000 10011100010000       23420      2710       EKG        NTP
     10001 10011100010001       23421      2711       EKH        NTQ
     10002 10011100010010       23422      2712       EKI        NTR
     10003 10011100010011       23423      2713       EKJ        NTS
    200000 110000110101000000   606500     30D40      B9M8       KIVH
    200001 110000110101000001   606501     30D41      B9M9       KIVI
    200002 110000110101000010   606502     30D42      B9MA       KIVJ
    200003 110000110101000011   606503     30D43      B9MB       KIVK

Tom Kyte
November 28, 2011 - 8:56 am UTC

it is a bit Rube Goldberg like - but I guess it would work too :)




what about ...

Sokrates, November 24, 2011 - 9:28 am UTC 

select dbms_random.string('u',1) from dual connect by level<=power(26, 2) union
select dbms_random.string('u',2) from dual connect by level<=power(26, 3) union
select dbms_random.string('u',3) from dual connect by level<=power(26, 4)

?
Tom Kyte
November 28, 2011 - 9:42 am UTC

what happens when string('u',1) randomly decides to not generate X sometime?

It might not happen often, but it definitely can happen.


This approach would have to way overproduce in order to try to avoid that case - and then apply a distinct (which you did by using UNION)

@Sokrates: Cool solution

JOH, November 28, 2011 - 9:19 am UTC

I really like the brevity :-)

@JOH

Sokrates, November 28, 2011 - 9:54 am UTC 

select unique dbms_random.string('u',mod(level,3)+1) from dual connect by level<1e6

is even shorter.
Can you please compute the probablity that every possible combination is in the result set of this query for us ?
Tom Kyte
November 29, 2011 - 7:22 am UTC

It is greater than zero.

Let me turn your probability question around. Can you prove that it will return the required result on every run? Can you prove in short that it is correct? If it were used to guide an airplane flying by wire, would you fly on said airplane based on its output?

And you still have to way overproduce, then sort down to get the answer.


select unique dbms_random.string('u',mod(level,3)+1) from dual connect by level<1e6

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1220     25.76      26.82          0          0          0       18278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1222     25.78      26.84          0          0          0       18278
********************************************************************************
select i1, i2, i3, l,
           case when i3 > 0 then chr(i3+ascii('A')-1) end ||
           case when i2 > 0 then chr(i2+ascii('A')-1) end ||
       chr(i1+ascii('A')-1)
           str
  from (
select mod((level-1),27) i1,
       mod( trunc((level-0.1)/27), 27) i2,
           mod( trunc((level-0.1)/27/27), 27 ) i3,
           level l
  from dual
connect by level <= 20000
       )
 where i1 <> 0
   and NOT( l>= 27*27 and i2 = 0)
   and NOT( l>= 27*27*27 and i3 = 0)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1220      0.33       0.50          0          0          0       18278
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1222      0.33       0.50          0          0          0       18278

A bit less Rube Goldberg like

Jichao Li, November 28, 2011 - 7:43 pm UTC

It'll be a bit less Rube Goldberg like if you cut it down to calculate only the AA...AZ... stuff. :)

WITH lst AS
  (SELECT level AS l FROM dual CONNECT BY level < 200),
numlist(l, f26x, s26x) AS
  (SELECT l,
          floor((l - 1) / 26) AS f26x,
          rpad(chr(64 + mod(l - 1, 26) + 1), 100, ' ') AS s26x
     FROM lst
    UNION ALL
   SELECT l,
          DECODE(f26x, 0, 0, floor((f26x - 1) / 26)),
          DECODE(f26x, 0, s26x, chr(64 + mod(f26x - 1, 26) + 1) || trim(s26x))
     FROM numlist
    WHERE f26x ! = 0)
 SELECT l, trim(s26x) AS s26x 
   FROM numlist
  WHERE f26x = 0;

Sokrates, November 29, 2011 - 9:19 am UTC

Can you prove that it will return the required result on every run?

no, because it doesn't do it, the probablitity is less than 100%

Can you prove in short that it is correct?

no, but the probablility that it is correct is only a grain of sand less than 100%

If it were used to guide an airplane flying by wire, would you fly on said airplane based on its output?

I would create a view v to deliver the correct result. Then I would test the view over and over.
Finally, I would bring the view to production, where I would let it work day by day for some years without any exception.

Today, I would enter this airplane:
SQL> select * from v;
select * from v
              *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kghstack_free1], [kkpapiItOpen-pnumvec], [], [], [], [], [], [], [], [], [], []

Hurgl, but exactly that happened to me today ( this select used to work for ages in production, today it is broken by ORA-00600
SQL> alter session set "_and_pruning_enabled"=false;

Session altered.

SQL> select * from v;

no rows selected

)

So: how big is the probablitity that *your* (or any) statement will return the required result on every run?

always less than 100%
Tom Kyte
November 29, 2011 - 11:49 am UTC

but you would intentionally put into production something you KNOW can return the wrong answer by design?

Just asking?

no

Sokrates, November 29, 2011 - 12:47 pm UTC

but you would intentionally put into production something you KNOW can return the wrong answer by design?

no, of course I wouldn't

I just thought it would be a nice 99.999 % - solution of the original question

try this

James Su, December 01, 2011 - 7:42 pm UTC

WITH num(n,str) AS (
SELECT 1 n,CAST('A' AS VARCHAR2(20)) str FROM DUAL
UNION ALL
SELECT n+1
,CASE WHEN SUBSTR(str,-1)<>'Z' THEN
SUBSTR(str,1,LENGTH(str)-1)||CHR(ASCII(SUBSTR(str,-1))+1)
WHEN RTRIM(str,'Z') IS NULL THEN
LPAD('A',LENGTH(str)+1,'A')
ELSE SUBSTR(str,1,LENGTH(RTRIM(str,'Z'))-1)||CHR(ASCII(SUBSTR(str,LENGTH(RTRIM(str,'Z')),1))+1)
||LPAD('A',LENGTH(str)-LENGTH(RTRIM(str,'Z')),'A')
END
FROM num
WHERE n<1000
)
SELECT * FROM num;
;


SELECT *
FROM (SELECT LEVEL n FROM DUAL CONNECT BY LEVEL<=1000)
MODEL
DIMENSION BY (n)
MEASURES (CAST('A' AS VARCHAR2(20)) str)
RULES
(
str[any] ORDER BY n
=CASE WHEN CV(n)=1 THEN 'A'
WHEN SUBSTR(str[CV()-1],-1)<>'Z' THEN
SUBSTR(str[CV()-1],1,LENGTH(str[CV()-1])-1)
||CHR(ASCII(SUBSTR(str[CV()-1],-1))+1)
WHEN RTRIM(str[CV()-1],'Z') IS NULL THEN
LPAD('A',LENGTH(str[CV()-1])+1,'A')
ELSE SUBSTR(str[CV()-1],1,LENGTH(RTRIM(str[CV()-1],'Z'))-1)
||CHR(ASCII(SUBSTR(str[CV()-1],LENGTH(RTRIM(str[CV()-1],'Z')),1))+1)
||LPAD('A',LENGTH(str[CV()-1])-LENGTH(RTRIM(str[CV()-1],'Z')),'A')
END
)
;


SELECT *
FROM DUAL
MODEL
DIMENSION BY (1 n)
MEASURES (CAST('A' AS VARCHAR2(20)) str)
RULES
ITERATE (1000)
(
str[ITERATION_NUMBER+1]
=CASE WHEN CV(n)=1 THEN 'A'
WHEN SUBSTR(str[CV()-1],-1)<>'Z' THEN
SUBSTR(str[CV()-1],1,LENGTH(str[CV()-1])-1)
||CHR(ASCII(SUBSTR(str[CV()-1],-1))+1)
WHEN RTRIM(str[CV()-1],'Z') IS NULL THEN
LPAD('A',LENGTH(str[CV()-1])+1,'A')
ELSE SUBSTR(str[CV()-1],1,LENGTH(RTRIM(str[CV()-1],'Z'))-1)
||CHR(ASCII(SUBSTR(str[CV()-1],LENGTH(RTRIM(str[CV()-1],'Z')),1))+1)
||LPAD('A',LENGTH(str[CV()-1])-LENGTH(RTRIM(str[CV()-1],'Z')),'A')
END
)
;


Solutions are always more than problems

Jichao Li, December 01, 2011 - 8:37 pm UTC

James just told us that solutions are always more than problems. :)

Nice SQLs.

Expand string

A reader, February 24, 2012 - 2:13 pm UTC

On a similar note, if I have the following "string" 
1,3,5,7:27,29,31,32:43
how can this be expanded to list all the numbers that are covered i.e. 1,3,5,7,8,...27,29,31,32,33,34,...43.

Thanks
Tom Kyte
February 25, 2012 - 6:30 am UTC 

ops$tkyte%ORA11GR2> variable txt varchar2(4000)
ops$tkyte%ORA11GR2> exec :txt := '1,3,5,7:27,29,31,32:43'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> column token format a10
ops$tkyte%ORA11GR2> column p1 format a10
ops$tkyte%ORA11GR2> column p2 format a10
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   ),
 15   data2 as
 16  (select token, substr( token, 1, instr( token||':', ':' )-1 ) p1,
 17                 nvl( substr( token, instr( token||':', ':')+1 ), token)  p2
 18     from data)
 19  select token, p1+column_value
 20    from data2, table( cast( multiset( select level-1 R from dual connect by level <= p2-p1+1)  as sys.odciNumberList) )
 21  /

TOKEN      P1+COLUMN_VALUE
---------- ---------------
1                        1
3                        3
5                        5
7:27                     7
7:27                     8
7:27                     9
7:27                    10
7:27                    11
7:27                    12
7:27                    13
7:27                    14
7:27                    15
7:27                    16
7:27                    17
7:27                    18
7:27                    19
7:27                    20
7:27                    21
7:27                    22
7:27                    23
7:27                    24
7:27                    25
7:27                    26
7:27                    27
29                      29
31                      31
32:43                   32
32:43                   33
32:43                   34
32:43                   35
32:43                   36
32:43                   37
32:43                   38
32:43                   39
32:43                   40
32:43                   41
32:43                   42
32:43                   43

38 rows selected.

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   ),
 15   data2 as
 16  (select token, substr( token, 1, instr( token||':', ':' )-1 ) p1,
 17                 nvl( substr( token, instr( token||':', ':')+1 ), token)  p2
 18     from data)
 19  select listagg( p1+column_value, ',' ) within group ( order by p1+column_value)
 20    from data2, table( cast( multiset( select level-1 R from dual connect by level <= p2-p1+1)  as sys.odciNumberList) )
 21  /

LISTAGG(P1+COLUMN_VALUE,',')WITHINGROUP(ORDERBYP1+COLUMN_VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,3,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,29,31,32,33,34,35,36,37,38,39,40,41,42,43

Fantastic!

A reader, February 25, 2012 - 9:12 pm UTC

Great, need to spend some time breaking it apart to understand how it works.

I know that LISTAGG was added in 11gR2 but will the first example work in 11gR1? Thanks
Tom Kyte
February 26, 2012 - 12:07 am UTC

listagg is 11gr2, you didn't specify a version so I just assume any version I want to.

search this site for stragg - an aggregate you can use in 11gr1 and before.



ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   ),
 15   data2 as
 16  (select token, substr( token, 1, instr( token||':', ':' )-1 ) p1,
 17                 nvl( substr( token, instr( token||':', ':')+1 ), token)  p2
 18     from data),
 19  data3 as
 20  (select p1+column_value num, row_number() over (order by p1+column_value) rn
 21     from data2, table( cast( multiset( select level-1 R from dual connect by level <= p2-p1+1)
 22     as sys.odciNumberList) )
 23  )
 24  select max(sys_connect_by_path(num, ',' )) str
 25    from data3
 26   start with rn = 1
 27  connect by prior rn+1 = rn
 28  /

STR
--------------------------------------------------
,1,3,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,2
2,23,24,25,26,27,29,31,32,33,34,35,36,37,38,39,40,
41,42,43


will work also in 10g and above.

Answers are not working

Ranjith, January 17, 2018 - 5:46 pm UTC

Executed the query given in oracle and it is not working.
Chris Saxon
January 17, 2018 - 8:58 pm UTC

What is not working? What did you do and why is the output not what you expect?

A reader, February 11, 2020 - 11:19 am UTC

i need function to for alpha sequence like i input number and it return alpha sequence like
select alpha(1)
from dual
and then output is A;
Connor McDonald
February 17, 2020 - 1:41 am UTC 

SQL> create or replace
  2  function alpha(i int) return varchar2 is
  3  begin
  4    if i = 1 then return 'A'; end if;
  5  end;
  6  /

Function created.

SQL>
SQL> select alpha(1) from dual;

ALPHA(1)
-------------------------------------------------
A


but seriously...we might need some better requirements. Otherwise I can make up just about anything

SQL> create or replace
  2  function alpha(i int) return varchar2 is
  3    tmp int := i;
  4    str varchar2(100);
  5  begin
  6    loop
  7      str := str || chr(mod(tmp-1,26)+65);
  8      tmp := floor(tmp / 26);
  9      exit when tmp = 0;
 10    end loop;
 11    return str;
 12  end;
 13  /

Function created.

SQL>
SQL> select alpha(1) from dual;

ALPHA(1)
------------------------------------------------------
A

SQL> select alpha(17) from dual;

ALPHA(17)
------------------------------------------------------
Q

SQL> select alpha(123) from dual;

ALPHA(123)
------------------------------------------------------
SD

SQL> select alpha(123456) from dual;

ALPHA(123456)
------------------------------------------------------
HPZG


Fentastic.

Suresh, July 07, 2020 - 11:08 am UTC

Its really useful. Can you please help me get 5 digit alphanumeric value for given number. eg.
if I pass 1 it returns A0001 , and for 2 it returns A0002 ............ZZZZ1 etc.

Thanks

I am looking for a way of creating a sequence in PL/SQL such a way that:

Ashik Skaria Shaji, June 01, 2023 - 9:58 am UTC

Below is the sequencing logic:

A000 --> first sequence A999 B000 . . . Z999 0A00 . 0A99 . 9A99 0B00 . 9Z99 00A0 . 99Z9 000A . 999Z --> last sequence

I want the code for this in PL/SQL . Please help to find solution for these query…
Connor McDonald
June 02, 2023 - 12:53 am UTC

Can you elaborate a little more on the precise system you are after.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.