Spell a Number in PL/SQL

[mdee]

Spell a Number in PL/SQL

Select To_Char(To_Date(To_Char(Floor(:Amt)),'J'),'JSP')
||' AND '||To_Char(To_Date(To_Char((:Amt-Floor(:Amt))*100),'J'),'JSP')
From Dual

Execute in Toad

Input :Amt as 38718.17

Output: THIRTY-EIGHT THOUSAND SEVEN HUNDRED EIGHTEEN AND SEVENTEEN

But this has a max limit 5373484.99
Actually query use Julian date conversion that must be between 1 and 5373484

Need spell out any number? Use a function.
Any one need Oracle Function please request, I will get it for you.

If you like this post leave a comment and add to my reputation, thanks!

[samu123]

thanks for the post...

yes bro please give me oracle function

thanks a lot

[mdee]

Here is the script:

--
FUNCTION F_SpellNumber (
In_Amt number)
RETURN CHAR IS
-- Spell Number a passed in, deimals are converted into digits in 100th
-- By : mdee
-- Version : 1.0 11-Feb-2006
type myArray is table of varchar2(100);
l_str myArray := myArray( '',' thousand ', ' million ',' billion ',
' trillion ',' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',' octillion ',
' nonillion ',' decillion ', ' undecillion ',' duodecillion ' );
l_num varchar2(100) default trunc(In_Amt);
l_return varchar2(1000);
l_deci_val varchar2(100);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
l_return := initcap(NVL(l_return,'xx'));
if instr(In_amt,'.') > 0 then
l_deci_val := substr(In_Amt,instr(In_Amt,'.')+1,2);
if length(l_deci_val)= 1 then
if l_deci_val >=1 and l_deci_val < 10 then
-- Convert decimal tens with trailing zero
l_deci_val := l_deci_val * 10;
end if;
end if;
l_return := l_return || ' and '||(l_deci_val)||'/100';
end if;
l_return := l_return || ' only.';
return(l_return);
END;

--
Need any help please let me know.

Try this:

Select F_SpellNumber(12345678901234567890) from dual

Twelve Quintillion Three Hundred Forty-Five Quadrillion Six Hundred Seventy-Eight Trillion Nine Hundred One Billion Two Hundred Thirty-Four Million Five Hundred Sixty-Seven Thousand Eight Hundred Ninety only.

[samu123]

thanks machan....

[mdee]

thanks machan....

You are welcome. Need help do write.

[amila325]

wow dis is gud... but I'm nt a DBA but i write those in java or c++ or some other language... anyway thanks 4 da post it's kind of gud 2 knw dat those things cn b done directly through sql :) :)

[blackroses]

intersting.............. thnxx broooooooooooo.......

[mdee]

wow dis is gud... but I'm nt a DBA but i write those in java or c++ or some other language... anyway thanks 4 da post it's kind of gud 2 knw dat those things cn b done directly through sql :) :)

You are welcome pal. There are lots of PL/SQL goodies. I too not a DBA, but much conversant with back-end as involve in larger systems designs. Need any help please write. I am happy to share knowledge.