Re: convert number to word ???

From: AK <oramagic_at_hotmail.com>
Date: Fri, 22 Aug 2003 14:09:26 -0800
Message-ID: <F001.005CC834.20030822140926@fatcity.com>

some where I got this

create or replace
function spell_number( p_number in number ) return varchar2
as
type myArray is table of varchar2(255); l_str myArray := myArray( '',

' thousand ', ' million ', 
' billion ', ' trillion ', 
' quadrillion ', ' quintillion ', 
' sextillion ', ' septillion ', 
' octillion ', ' nonillion ', 
' decillion ', ' undecillion ', 
' duodecillion ' ); 

l_num varchar2(50) default trunc( p_number ); l_return varchar2(4000);
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;

return l_return;
end;
/
SQL> select
2 spell_number( 12345678901234567890123456789012345678 ) 3 from dual;

SPELL_NUMBER(1234567890123456789012345678901234567

---

Twelve undecillion Three Hundred Forty-Five decill ion Six Hundred Seventy-Eight nonillion Nine Hundr ed One octillion Two Hundred Thirty-Four septillio n Five Hundred Sixty-Seven sextillion Eight Hundre d Ninety quintillion One Hundred Twenty-Three quad rillion Four Hundred Fifty-Six trillion Seven Hund red Eighty-Nine billion Twelve million Three Hundr ed Forty-Five thousand Six Hundred Seventy-Eight

a-k

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, August 22, 2003 2:19 PM

> Hi,
>
> Is there a function that can conver number to word?
> ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND
> THREE. Thank you in advanced!!!
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Andrea Oracle
> INET: andreaoracle_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: oramagic_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 22 2003 - 17:09:26 CDT