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