6 Replies Latest reply on Oct 20, 2008 10:55 PM by William Robertson

    Alphanumeric sequence number generator

    416312
      How do I generate a alphanumeric sequence number in oracle?

      Like this:
      0000
      0001
      0002
      ......
      9999
      A000
      A001
      A002
      .....
      A999
      B000
      B001
      B002
      .....
      .....
      Z999
      AB00
      AB01
      AB02
      .....
        • 1. Re: Alphanumeric sequence number generator
          Satyaki_De
          I've two questions.

          1. When the series will end?

          2. AA00 is missing in your example. Why?

          I don't think - that can be done using SQL - i guess.

          Regards.

          Satyaki De.
          • 2. Re: Alphanumeric sequence number generator
            William Robertson
            Could it go

            1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, ... Z
            10, 11, 12, 13, 14,15, 16, 17, 18, 19, 1A, 1B, 1C ...

            That would just be a normal sequence converted into base 36.
            • 3. Re: Alphanumeric sequence number generator
              416312
              Could you elaborate how a normal sequence can be converted into base 36?
              • 4. Re: Alphanumeric sequence number generator
                BluShadow
                MWJ wrote:
                Could you elaborate how a normal sequence can be converted into base 36?
                Not completely tested, but this will give you a 4 digit base 36 number...
                SQL> ed
                Wrote file afiedt.buf
                
                  1  with b36 as (select '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' as b36 from dual)
                  2      ,num as (select &num as num from dual)
                  3  --
                  4  select substr(b36.b36, trunc(num.num/(36*36*36))+1, 1)||
                  5         substr(b36.b36, mod(trunc(num.num/(36*36)),36)+1,1)||
                  6         substr(b36.b36, mod(trunc(num.num/36),36)+1,1)||
                  7         substr(b36.b36, mod(num.num,36)+1,1)
                  8* from num, b36
                SQL> /
                Enter value for num: 1234
                old   2:     ,num as (select &num as num from dual)
                new   2:     ,num as (select 1234 as num from dual)
                
                SUBS
                ----
                00YA
                
                SQL>
                • 5. Re: Alphanumeric sequence number generator
                  BluShadow
                  Forgot to say, if you want it to wrap around again to 0000 when it reaches ZZZZ then you'll need to mod the first element by 36 too...
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with b36 as (select '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' as b36 from dual)
                    2      ,num as (select rownum+1679600 as num from dual connect by rownum <= 50)
                    3  --
                    4  select num.num,
                    5         substr(b36.b36, mod(trunc(num.num/(36*36*36)),36)+1, 1)||
                    6         substr(b36.b36, mod(trunc(num.num/(36*36)),36)+1,1)||
                    7         substr(b36.b36, mod(trunc(num.num/36),36)+1,1)||
                    8         substr(b36.b36, mod(num.num,36)+1,1)
                    9* from num, b36
                  SQL> /
                  
                         NUM SUBS
                  ---------- ----
                     1679601 ZZZL
                     1679602 ZZZM
                     1679603 ZZZN
                     1679604 ZZZO
                     1679605 ZZZP
                     1679606 ZZZQ
                     1679607 ZZZR
                     1679608 ZZZS
                     1679609 ZZZT
                     1679610 ZZZU
                     1679611 ZZZV
                     1679612 ZZZW
                     1679613 ZZZX
                     1679614 ZZZY
                     1679615 ZZZZ
                     1679616 0000
                     1679617 0001
                     1679618 0002
                     1679619 0003
                     1679620 0004
                     1679621 0005
                     1679622 0006
                     1679623 0007
                     1679624 0008
                     1679625 0009
                     1679626 000A
                     1679627 000B
                     1679628 000C
                     1679629 000D
                     1679630 000E
                     1679631 000F
                     1679632 000G
                     1679633 000H
                     1679634 000I
                     1679635 000J
                     1679636 000K
                     1679637 000L
                     1679638 000M
                     1679639 000N
                     1679640 000O
                     1679641 000P
                     1679642 000Q
                     1679643 000R
                     1679644 000S
                     1679645 000T
                     1679646 000U
                     1679647 000V
                     1679648 000W
                     1679649 000X
                     1679650 000Y
                  
                  50 rows selected.
                  
                  SQL>
                  • 6. Re: Alphanumeric sequence number generator
                    William Robertson
                    There's also a <tt>to_base()</tt> function in my ill-assorted "utilities" package here. I forget what else is in there or how it works.

                    Edited by: William Robertson on Oct 20, 2008 11:52 PM
                    Just playing around with it and trying to get it to spell 'BOOBIES' I notice it's got a few bugs and limitations, so be warned.