• Questions
• # Function for alphabetical sequence like a spreadsheet

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

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)

We're not taking reviews currently, so please try again later if you want to add a review.

# Reviews

February 12, 2020 - 12:32 pm UTC

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

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

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

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

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;

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

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;

Followup

February 13, 2020 - 11:36 am UTC

Great!

# More to Explore

##### SQL

The Oracle documentation contains a complete SQL reference.