Home » SQL & PL/SQL » SQL & PL/SQL » numeric to sring
numeric to sring [message #37294] Wed, 30 January 2002 03:54 Go to next message
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 sring [message #37295 is a reply to message #37294] Wed, 30 January 2002 04:16 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
select to_char( to_date( substr( to_char(546666),1),'j'),'Jsp') from dual;

that is the maximum number you can convert to words.
Re: numeric to string - larger number [message #37299 is a reply to message #37294] Wed, 30 January 2002 04:57 Go to previous message
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
Previous Topic: handling error
Next Topic: how do i insert a value like 'Help'm pls' into a varchar column
Goto Forum:
  


Current Time: Sun May 24 18:05:40 CDT 2020