[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1722
  • Last Modified:

alphabetic sequence generation in oracle

I need unique alphabeticals sequence generation for about 5000 in oracle, how do I do that
0
reddi_mk
Asked:
reddi_mk
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please clarify the request?
0
 
reddi_mkAuthor Commented:
I need to generate unique alphabeticals like A B C...Z , AA AB AB...AZ, BA,BB,BC...BZ  up to 5 characters
Could you please send me the script for this.
0
 
reddi_mkAuthor Commented:
I need this in pl/sql script.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
reddi_mkAuthor Commented:
i had following script can somebody tweak it.

declare
v_Current_Number number;
v_Current_Mod_Value number;
v_Current_Quot_Value number;
v_third_char varchar2(200);
v_second_char varchar2(200);
v_first_char varchar2(200);
v_Final_String varchar2(200);
     begin
select CINXFER.Testseq.nextval into v_Current_Number from dual;
v_Current_Mod_Value := mod(v_Current_Number, 26);
v_Current_Quot_Value := trunc(v_Current_Number/26);
select chr(64+ decode(v_Current_Mod_Value, 0, 26, v_Current_Mod_Value)) into v_third_char from dual;
dbms_output.put_line('third_char '||v_third_char);
select decode(v_Current_Quot_Value, 0, NULL,chr(64+ decode(mod(v_Current_Quot_Value, 26), 0, 26, mod(v_Current_Quot_Value, 26)))) into v_second_char from dual;
select decode(trunc(v_Current_Number/(26*26)), 0, NULL, chr(64+ decode(mod(trunc(v_Current_Number/(26*26)), 26), 0, 26, mod(trunc(v_Current_Number/(26*26)), 26)))) into v_first_char from dual;
v_Final_String := v_first_char||v_second_char||v_third_char;
end;

Open in new window

0
 
reddi_mkAuthor Commented:
Can somebody help me I need to get this done by end of today.
0
 
DiscoNovaCommented:
So, basically you're looking for a Base10 to Base26 conversion function?

http://www.orafaq.com/forum/t/96699/2/ offers a few possible solutions.
0
 
DiscoNovaCommented:
i strongly object the fact that users have, during the last couple of months, started to abuse EE's "request removal"-option instead of awarding points to the people responding to their questions.

The page I linked to gives not only one, but a multitude of distinct possibilities of solving the problem. All the author needed to do is choose the one that suits him best.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now