Re: Change number to word
Date: 31 Jul 2001 08:10:01 GMT
Message-ID: <9k5p4p$3q8$1_at_news.liv.ac.uk>
[Quoted] ,In comp.databases.oracle.misc Theresa Wong <kywong_at_chinahkphoto.com.hk> wrote:
: Hi,
: Do anyone have soultion about changing number to word (123,456,789 --> One
: hundred million ......) in oracle SQL or PL/SQL.
: Regards,
: Theresa
[Quoted] Here's a solution once posted by Thomas Kyte to spell large numbers:
[Quoted] Sure, with a little effort -- you can use the J -> Jsp trick and do it yourself.
ps$tkyte_at_DEV816> create or replace
2 function spell_number( p_number in number )
3 return varchar2
4 as
5 type myArray is table of varchar2(255); 6 l_str myArray := myArray( '', 7 ' thousand ', ' million ', 8 ' billion ', ' trillion ', 9 ' quadrillion ', ' quintillion ', 10 ' sextillion ', ' septillion ', 11 ' octillion ', ' nonillion ', 12 ' decillion ', ' undecillion ', 13 ' duodecillion ' ); 14 15 l_num varchar2(50) default trunc( p_number ); 16 l_return varchar2(4000); 17 begin 18 for i in 1 .. l_str.count 19 loop 20 exit when l_num is null; 21 22 l_return := to_char( 23 to_date( 24 substr(l_num, length(l_num)-2, 3), 25 'J' ), 26 'Jsp' ) || l_str(i) || l_return; 27 28 l_num := substr( l_num, 1, length(l_num)-3 ); 29 end loop; 30 31 return l_return;
32 end;
33 /
Function created.
ops$tkyte_at_DEV816>
ops$tkyte_at_DEV816> 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
ops$tkyte_at_DEV816>
ops$tkyte_at_DEV816> declare
2 l_num number; 3 l_str varchar2(255); 4 l_spelled varchar2(4000); 5 begin 6 for i in 1 .. 10 7 loop 8 l_num := random.rand() || 9 random.rand() || 10 random.rand(); 11 l_str := 12 to_char( l_num, '999,999,999,999,999,999' ) ; 13 l_spelled := spell_number( l_num ); 14 15 dbms_output.put_line 16 ( l_str || ' -- ' || l_spelled ); 17 end loop;
18 end;
19 /
312,051,345,415,411 -- Three Hundred Twelve trillion Fifty-One billion Three Hundred Forty-Five million Four Hundred Fifteen thousand Four Hundred Eleven
25,131,160,032,468 -- Twenty-Five trillion One Hundred Thirty-One billion One Hundred Sixty million Thirty-Two thousand Four Hundred Sixty-Eight
210,202,025,424,018 -- Two Hundred Ten trillion Two Hundred Two billion Twenty-Five million Four Hundred Twenty-Four thousand Eighteen
28,221,169,384,374 -- Twenty-Eight trillion Two Hundred Twenty-One billion One Hundred Sixty-Nine million Three Hundred Eighty-Four thousand Three Hundred Seventy-Four
148,881,759,131,997 -- One Hundred Forty-Eight trillion Eight Hundred Eighty-One billion Seven Hundred Fifty-Nine million One Hundred Thirty-One thousand Nine Hundred Ninety-Seven
178,761,729,314,960 -- One Hundred Seventy-Eight trillion Seven Hundred Sixty-One billion Seven Hundred Twenty-Nine million Three Hundred Fourteen thousand Nine Hundred Sixty
7,789,111,094,989 -- Seven trillion Seven Hundred Eighty-Nine billion One Hundred Eleven million Ninety-Four thousand Nine Hundred Eighty-Nine 137,652,698,329,777 -- One Hundred Thirty-Seven trillion Six Hundred Fifty-Two billion Six Hundred Ninety-Eight million Three Hundred Twenty-Nine thousand Seven Hundred Seventy-Seven
155,311,009,417,668 -- One Hundred Fifty-Five trillion Three Hundred Eleven billion Nine million Four Hundred Seventeen thousand Six Hundred Sixty-Eight
139,292,540,822,249 -- One Hundred Thirty-Nine trillion Two Hundred Ninety-Two billion Five Hundred Forty million Eight Hundred Twenty-Two thousand Two Hundred Forty-Nine
PL/SQL procedure successfully completed.
ops$tkyte_at_DEV816>
Helen Received on Tue Jul 31 2001 - 10:10:01 CEST