numeric to sring [message #37294] |
Wed, 30 January 2002 03:54 |
Gopal Raw
Messages: 19 Registered: January 2002
|
Junior Member |
|
|
please give me the plsql code to convert a number into
words.
suppose : i want to convert 100 into words.
like One Hundreds
|
|
|
|
Re: numeric to string - larger number [message #37299 is a reply to message #37294] |
Wed, 30 January 2002 04:57 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Here is a nifty function to go above the Julian limit. Credit for this goes to "Ask" Tom Kyte.
create or replace
function sf_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;
/
09:50:04 ==> select sf_spell_Number(78329972923) from dual;
SF_SPELL_NUMBER(78329972923)
----------------------------------------------------------------------------------------------------
Seventy-Eight billion Three Hundred Twenty-Nine million Nine Hundred Seventy-Two thousand Nine Hundr
ed Twenty-Three
|
|
|