Skip to Main Content
  • Questions
  • Function for alphabetical sequence like a spreadsheet

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rais.

Asked: February 12, 2020 - 4:44 am UTC

Answered by: Chris Saxon - Last updated: February 13, 2020 - 11:36 am UTC

Category: PL/SQL - Version: 11.0.2

Viewed 1000+ times

You Asked

I need function which convert numeric to alphabet like when I input 1 then it will return 'A', when i input 2 then it will return 'B' please help me on this.

and we said...

So after Z it goes AA, AB, etc?

You can use CHR to convert ascii numbers to characters. A is 65. So you can get

- 1st character with the mod ( row, 26 )
- 2nd with mod ( floor ( row / 26 ), 26 )
- 3rd with mod ( floor ( row / 676 ), 26 )
- etc.

You need to do some jiggering of the rowv number to account for the second column starting on the 27th character, the third on 703rd character, etc.

e.g.

select level, 
       case
         when level <= 702 then null
         else chr ( mod ( floor ( ( level - 702 - 1 ) / 676 ), 26 ) + 65 )
       end || 
       case
         when level <= 26 then null
         else chr ( mod ( floor ( ( level - 26 - 1 ) / 26 ), 26 ) + 65 )
       end || 
       chr (  mod ( level - 1, 26 ) + 65 ) ch
from   dual
connect by level <= 18278;

LEVEL   CH   
       1 A     
       2 B    
...        
      25 Y     
      26 Z     
      27 AA    
      28 AB    
...
      51 AY    
      52 AZ    
      53 BA    
      54 BB    
...
     701 ZY     
     702 ZZ     
     703 AAA    
     704 AAB 
...
   18277 ZZY    
   18278 ZZZ 

and you rated our response

  (4 ratings)

Reviews

February 12, 2020 - 12:32 pm UTC

Reviewer: A reader

thanks for your response but i need a function wanted convert one value at a time.
Chris Saxon

Followup  

February 12, 2020 - 1:44 pm UTC

So how have you tried turning this into a function?

I'm sure you can do it. Go on, try it!

Converted to function

February 12, 2020 - 12:51 pm UTC

Reviewer: A reader

create or replace view num_to_chr as select level num, 
       case
         when level <= 702 then null
         else chr ( mod ( floor ( ( level - 702 - 1 ) / 676 ), 26 ) + 65 )
       end || 
       case
         when level <= 26 then null
         else chr ( mod ( floor ( ( level - 26 - 1 ) / 26 ), 26 ) + 65 )
       end || 
       chr (  mod ( level - 1, 26 ) + 65 ) ch
from   dual
connect by level <= 18278;


create or replace function return_chr(p_num in number) return varchar2 as 

l_return varchar2(2000);
begin

select ch into l_return from num_to_chr where num=p_num;
return l_return;

end;



Chris Saxon

Followup  

February 12, 2020 - 1:45 pm UTC

If it's just a matter of computing the character from the input, I'd prefer extract the logic and do it all in PL/SQL. Otherwise you have to compute the result for all earlier values too.

February 13, 2020 - 5:02 am UTC

Reviewer: A reader

yes i made it following function thanks once again

create or replace FUNCTION get_abc (ser number)
RETURN CHAR AS
ch varchar2(5);
begin
begin
select case
when ser <= 702 then null
else chr ( mod ( floor ( ( ser - 702 - 1 ) / 676 ), 26 ) + 65 )
end ||
case
when ser <= 26 then null
else chr ( mod ( floor ( ( ser - 26 - 1 ) / 26 ), 26 ) + 65 )
end ||
chr ( mod ( ser - 1, 26 ) + 65 )
into ch
from dual;
end;
return (ch);
end;
Chris Saxon

Followup  

February 13, 2020 - 10:06 am UTC

Great!

NB - this is pure computation; there's no need to make it a SQL statement! You can assign the case expression to the return variable:

ch :=  case
  when ser <= 702 then null
...

February 13, 2020 - 10:41 am UTC

Reviewer: A reader

done.

create or replace FUNCTION get_abc (ser number)
RETURN CHAR AS
ch varchar2(5);
begin
begin
ch := case
when ser <= 702 then null
else chr ( mod ( floor ( ( ser - 702 - 1 ) / 676 ), 26 ) + 65 )
end ||
case
when ser <= 26 then null
else chr ( mod ( floor ( ( ser - 26 - 1 ) / 26 ), 26 ) + 65 )
end ||
chr ( mod ( ser - 1, 26 ) + 65 ) ;
end;
return (ch);
end;
Chris Saxon

Followup  

February 13, 2020 - 11:36 am UTC

Great!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.