CODEWITHSUNDEEP
sqloracle-databaseplsql
Alexandru
Alexandru

Reputation: 67

Oracle alphanumeric sequence

I need to create an alphanumeric sequence in oracle but can't figure that out. Already checked the existing questions, but not fully understand it (my sql knowledge is not that great).

The number 00001 will increase up to 00009 then new series will start with 00000A and will increase up to 00000Z.

Then the new series will start with 0000A1 and will increase up to 0000A9, after this number the new number will start with 0000AA and go up to 0000AZ.

Then comes the number 0000B1 which goes upto 0000B9 then new series start with 0000BA till 0000BZ and so on.

As well the sequence should have a prefix, for example CNT00000Z, CNT0000A1

I've tried to test different setups using a wrapper function with different oracle converting functions like substr, mod, to_char, etc. but with no success.

If you have any ideas I would be grateful.

Cheers, Alexandru

Upvotes: 1

Views: 547

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Well...without a zero and I and O, then it would be custom base33 not base34. To do a "legitimate" version where the digits/letters ascend in the normal fashion no matter what decimal position you are at, you can do this

SQL> create or replace
  2  function int_to_seq(n in integer ) return varchar2    as
  3     ret       varchar2(30);
  4     quotient  integer;
  5     digit     char(1);
  6     chars varchar2(100) := '123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
  7     len int := length(chars)+1;
  8  begin
  9     quotient := n;
 10     while quotient > 0
 11     loop
 12         ret := substr(chars,mod(quotient,len),1) || ret;
 13         quotient := floor(quotient/len);
 14     end loop ;
 15     return 'CNT'||lpad(ret, 10, '0');
 16  end ;
 17  /

Function created.

SQL>
SQL> select rownum, int_to_seq(rownum) x
  2  from dual
  3  connect by level <= 36
  4  /

    ROWNUM X
---------- ----------------------------------------
         1 CNT0000000001
         2 CNT0000000002
         3 CNT0000000003
         4 CNT0000000004
         5 CNT0000000005
         6 CNT0000000006
         7 CNT0000000007
         8 CNT0000000008
         9 CNT0000000009
        10 CNT000000000A
        11 CNT000000000B
        12 CNT000000000C
        13 CNT000000000D
        14 CNT000000000E
        15 CNT000000000F
        16 CNT000000000G
        17 CNT000000000H
        18 CNT000000000J
        19 CNT000000000K
        20 CNT000000000L
        21 CNT000000000M
        22 CNT000000000N
        23 CNT000000000P
        24 CNT000000000Q
        25 CNT000000000R
        26 CNT000000000S
        27 CNT000000000T
        28 CNT000000000U
        29 CNT000000000V
        30 CNT000000000W
        31 CNT000000000X
        32 CNT000000000Y
        33 CNT000000000Z
        34 CNT0000000011
        35 CNT0000000011
        36 CNT0000000012

36 rows selected.

But if you really want the 2nd power onwards to start with "A", but the final place going through all 33 characters, then you can do this

SQL> create or replace
  2  function int_to_seq2(n in integer ) return varchar2    as
  3     ret       varchar2(30);
  4     quotient  integer;
  5     digit     char(1);
  6     chars varchar2(100) := '123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
  7     chars2 varchar2(100) := 'ABCDEFGHJKLMNPQRSTUVWXYZ';
  8     len int := length(chars)+1;
  9  begin
 10     quotient := n;
 11     ret := substr(chars,mod(quotient,len),1) || ret;
 12     quotient := floor(quotient/len);
 13     len := length(chars2)+1;
 14     while quotient > 0
 15     loop
 16         ret := substr(chars2,mod(quotient,len),1) || ret;
 17         quotient := floor(quotient/len);
 18     end loop ;
 19     return 'CNT'||lpad(ret, 10, '0');
 20  end ;
 21  /

Function created.

SQL>
SQL> select rownum, int_to_seq2(rownum) x
  2  from dual
  3  connect by level <= 100
  4  /

    ROWNUM X
---------- ----------------------------------------
         1 CNT0000000001
         2 CNT0000000002
         3 CNT0000000003
         4 CNT0000000004
         5 CNT0000000005
         6 CNT0000000006
         7 CNT0000000007
         8 CNT0000000008
         9 CNT0000000009
        10 CNT000000000A
        11 CNT000000000B
        12 CNT000000000C
        13 CNT000000000D
        14 CNT000000000E
        15 CNT000000000F
        16 CNT000000000G
        17 CNT000000000H
        18 CNT000000000J
        19 CNT000000000K
        20 CNT000000000L
        21 CNT000000000M
        22 CNT000000000N
        23 CNT000000000P
        24 CNT000000000Q
        25 CNT000000000R
        26 CNT000000000S
        27 CNT000000000T
        28 CNT000000000U
        29 CNT000000000V
        30 CNT000000000W
        31 CNT000000000X
        32 CNT000000000Y
        33 CNT000000000Z
        34 CNT00000000A1
        35 CNT00000000A1
        36 CNT00000000A2
        37 CNT00000000A3
        38 CNT00000000A4
        39 CNT00000000A5
        40 CNT00000000A6
        41 CNT00000000A7
        42 CNT00000000A8
        43 CNT00000000A9
        44 CNT00000000AA
        45 CNT00000000AB
        46 CNT00000000AC
        47 CNT00000000AD
        48 CNT00000000AE
        49 CNT00000000AF
        50 CNT00000000AG
        51 CNT00000000AH
        52 CNT00000000AJ
        53 CNT00000000AK
        54 CNT00000000AL
        55 CNT00000000AM
        56 CNT00000000AN
        57 CNT00000000AP
        58 CNT00000000AQ
        59 CNT00000000AR
        60 CNT00000000AS
        61 CNT00000000AT
        62 CNT00000000AU
        63 CNT00000000AV
        64 CNT00000000AW
        65 CNT00000000AX
        66 CNT00000000AY
        67 CNT00000000AZ
        68 CNT00000000B1
        69 CNT00000000B1
        70 CNT00000000B2
        71 CNT00000000B3
        72 CNT00000000B4
        73 CNT00000000B5
        74 CNT00000000B6
        75 CNT00000000B7
        76 CNT00000000B8
        77 CNT00000000B9
        78 CNT00000000BA
        79 CNT00000000BB
        80 CNT00000000BC
        81 CNT00000000BD
        82 CNT00000000BE
        83 CNT00000000BF
        84 CNT00000000BG
        85 CNT00000000BH
        86 CNT00000000BJ
        87 CNT00000000BK
        88 CNT00000000BL
        89 CNT00000000BM
        90 CNT00000000BN
        91 CNT00000000BP
        92 CNT00000000BQ
        93 CNT00000000BR
        94 CNT00000000BS
        95 CNT00000000BT
        96 CNT00000000BU
        97 CNT00000000BV
        98 CNT00000000BW
        99 CNT00000000BX
       100 CNT00000000BY

100 rows selected.

SQL>
SQL>
SQL>

Upvotes: 2

Related Questions