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

Last updated: May 16, 2024 - 12:27 pm UTC

Version: 11.0.2

Viewed 10K+ times! This question is

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 Chris 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 

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 12, 2020 - 12:32 pm UTC

thanks for your response but i need a function wanted convert one value at a time.
Chris Saxon
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

A reader, February 12, 2020 - 12:51 pm UTC

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
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.

A reader, February 13, 2020 - 5:02 am UTC

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
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
...

A reader, February 13, 2020 - 10:41 am UTC

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
February 13, 2020 - 11:36 am UTC

Great!

Thanks

VC, May 16, 2024 - 9:27 am UTC

Thanks Chris. This is what I needed today!!.
Chris Saxon
May 16, 2024 - 12:27 pm UTC

You're welcome!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.