Re: Change number to word

From: Ms. D.H. Harvey <qq45_at_liverpool.ac.uk>
Date: 31 Jul 2001 08:10:01 GMT
Message-ID: <9k5p4p$3q8$1@news.liv.ac.uk>

,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

Here's a solution once posted by Thomas Kyte to spell large numbers:

---

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 - 03:10:01 CDT