Home » SQL & PL/SQL » SQL & PL/SQL » Alphanumeric Sequence Generator
Alphanumeric Sequence Generator [message #650753] Tue, 03 May 2016 02:23 Go to next message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
The sequence generator should be in the below format.Could anyone please help me how to write the code to generate the sequence in the below format in Oracle/DB2.
Kindly help me out as this is straight requirement in my project.
Thanks in Advance

46999 --> 46A01 , 46A02, 46A03 .........46A99 --> 46B01,46B02,46B03 ........46B99 --> 46C01, 46C02,46C03 ........46C99 etc

[Updated on: Tue, 03 May 2016 02:33]

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650756 is a reply to message #650753] Tue, 03 May 2016 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, for values between 46000 and 47599:
SQL> col a format a15
SQL> col b format a15
SQL> col c format a15
SQL> with
  2    vals as (
  3      select 46980+level-1 a, 47080+level-1 b, 47180+level-1 c
  4      from dual connect by level <= 30
  5    )
  6  select a||' -> 46'||
  7         case
  8           when a < 47000 then to_char(trunc(mod(a,46000)/100))
  9           else chr(ascii('A')+trunc(mod(a,46000)/100)-10)
 10         end||
 11         to_char(mod(a,100),'fm00') a,
 12         b||' -> 46'||
 13         case
 14           when b < 47000 then to_char(trunc(mod(a,46000)/100))
 15           else chr(ascii('A')+trunc(mod(b,46000)/100)-10)
 16         end||
 17         to_char(mod(b,100),'fm00') b,
 18         c||' -> 46'||
 19         case
 20           when c < 47000 then to_char(trunc(mod(a,46000)/100))
 21           else chr(ascii('A')+trunc(mod(c,46000)/100)-10)
 22         end||
 23         to_char(mod(b,100),'fm00') c
 24  from vals
 25  /
A               B               C
--------------- --------------- ---------------
46980 -> 46980  47080 -> 46A80  47180 -> 46B80
46981 -> 46981  47081 -> 46A81  47181 -> 46B81
46982 -> 46982  47082 -> 46A82  47182 -> 46B82
46983 -> 46983  47083 -> 46A83  47183 -> 46B83
46984 -> 46984  47084 -> 46A84  47184 -> 46B84
46985 -> 46985  47085 -> 46A85  47185 -> 46B85
46986 -> 46986  47086 -> 46A86  47186 -> 46B86
46987 -> 46987  47087 -> 46A87  47187 -> 46B87
46988 -> 46988  47088 -> 46A88  47188 -> 46B88
46989 -> 46989  47089 -> 46A89  47189 -> 46B89
46990 -> 46990  47090 -> 46A90  47190 -> 46B90
46991 -> 46991  47091 -> 46A91  47191 -> 46B91
46992 -> 46992  47092 -> 46A92  47192 -> 46B92
46993 -> 46993  47093 -> 46A93  47193 -> 46B93
46994 -> 46994  47094 -> 46A94  47194 -> 46B94
46995 -> 46995  47095 -> 46A95  47195 -> 46B95
46996 -> 46996  47096 -> 46A96  47196 -> 46B96
46997 -> 46997  47097 -> 46A97  47197 -> 46B97
46998 -> 46998  47098 -> 46A98  47198 -> 46B98
46999 -> 46999  47099 -> 46A99  47199 -> 46B99
47000 -> 46A00  47100 -> 46B00  47200 -> 46C00
47001 -> 46A01  47101 -> 46B01  47201 -> 46C01
47002 -> 46A02  47102 -> 46B02  47202 -> 46C02
47003 -> 46A03  47103 -> 46B03  47203 -> 46C03
47004 -> 46A04  47104 -> 46B04  47204 -> 46C04
47005 -> 46A05  47105 -> 46B05  47205 -> 46C05
47006 -> 46A06  47106 -> 46B06  47206 -> 46C06
47007 -> 46A07  47107 -> 46B07  47207 -> 46C07
47008 -> 46A08  47108 -> 46B08  47208 -> 46C08
47009 -> 46A09  47109 -> 46B09  47209 -> 46C09

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650758 is a reply to message #650756] Tue, 03 May 2016 03:17 Go to previous messageGo to next message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
Hi Sir,
Thanks a lot for your quick response. I just given the format above..but sorry i put you in wrong way. Kindly provide me the query to produce the below output.
Sorry for the inconvenience caused. Please help. Thanks

46999
46A01
46A02
46A03
-----
-----
46A99
46B01
46B02
46B03
-----
-----
46B99
46C01
46C02
-----
-----
46C99
46D01
46D02
-----
-----
46D99
----etc

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650760 is a reply to message #650758] Tue, 03 May 2016 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just use a normal sequence which gives a number and create a function which will use the formula in my query and return the string as my query.
In my query, I gave examples of execution of the formula for different values.
For instance, 47185 -> 46B85 means when the sequence gives 47185 the formula returns 46B85.

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650761 is a reply to message #650760] Tue, 03 May 2016 03:27 Go to previous messageGo to next message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
Will it displays in the below format...Kindly post the direct query as i am new to this background...Sorry for troubling you..but please

SEQ_NUM
46999
46A01
46A02
46A03
-----
-----
46A99
46B01
46B02
46B03
-----
-----
46B99
46C01
46C02
-----
-----
46C99
46D01
46D02
-----
-----
46D99
----etc

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650762 is a reply to message #650761] Tue, 03 May 2016 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't want the 46X00 values and skip them then you have to change the formula.
This is not an Oracle issue but more a logical/mathematical one. Tr to do it and post your tries if you are stuck.
You also have to define what you want if value if less than 46000 or what value should be after 46Z99.

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650763 is a reply to message #650762] Tue, 03 May 2016 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For instance, you can have a sequence and a function like this:
SQL> create sequence seq start with 46990;

Sequence created.

SQL> create or replace function next_val return varchar2
  2  is
  3    v integer;
  4  begin
  5    v := seq.nextval;
  6    if mod(v,100) = 0 then v := seq.nextval; end if;
  7    return '46'||
  8           case
  9             when v < 47000 then to_char(trunc(mod(v,46000)/100))
 10             else chr(ascii('A')+trunc(mod(v,46000)/100)-10)
 11           end||
 12           to_char(mod(v,100),'fm00');
 13  end;
 14  /

Function created.

SQL> select next_val from dual connect by level <= 20;
NEXT_VAL
------------------------------------------------------------------------
46990
46991
46992
46993
46994
46995
46996
46997
46998
46999
46A01
46A02
46A03
46A04
46A05
46A06
46A07
46A08
46A09
46A10

20 rows selected.

Report message to a moderator

Re: Alphanumeric Sequence Generator [message #650770 is a reply to message #650763] Tue, 03 May 2016 05:28 Go to previous message
msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad
Junior Member
Thanks a lot..Really this is helpful for me...

Report message to a moderator

Previous Topic: SQL Query to fetch not matching records
Next Topic: How to suppress messages from FTP server?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:25:15 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.