[Webinar] Streamline your web hosting managementRegister Today

  • 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
  • 4
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please clarify the request?
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.
reddi_mkAuthor Commented:
I need this in pl/sql script.
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.

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);
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;

Open in new window

reddi_mkAuthor Commented:
Can somebody help me I need to get this done by end of today.
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.
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.

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