Re: Change number to word

Nice, but can you do the reverse, ie change english character string to a number ?

"Ms. D.H. Harvey" <qq45_at_liverpool.ac.uk> wrote in message news:9k5p4p$3q8$1_at_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 - 13:25:30 CDT