Home » SQL & PL/SQL » SQL & PL/SQL » number to word ( first, second , third, forth ...etc)
number to word ( first, second , third, forth ...etc) [message #190965] Sun, 03 September 2006 08:26 Go to next message
ramzi_j_h
Messages: 36
Registered: September 2006
Location: Jordan
Member
please i wanna a library or a program unit that convert numbers to words, such as 1 convert it to FIRST , 2 to SECOND , 3 to THIRD , 4 to FORTH , and so on

please i need it as soon as possible

thanx alot in advance
Re: number to word ( first, second , third, forth ...etc) [message #190968 is a reply to message #190965] Sun, 03 September 2006 09:51 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
SELECT TO_CHAR(TO_DATE(<your_number>, 'J'),'JSPTH') FROM dual;
Re: number to word ( first, second , third, forth ...etc) [message #191094 is a reply to message #190965] Mon, 04 September 2006 10:05 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
You can put it in a function and use it as neatly AS LIKE THIS...

SQL> create or replace function num2word(num in number)
  2   return varchar2
  3   as
  4   begin
  5     return  to_char(to_date(num, 'j'),'JSPTH');
  6  END;
SQL> /

Function created.


SQL> select num2word(12345) from dual;

NUM2WORD(12345)
--------------------------------------------------------------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIFTH



Re: number to word ( first, second , third, forth ...etc) [message #191160 is a reply to message #191094] Tue, 05 September 2006 01:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Note that limits apply to the Julian date format.

MHE
Re: number to word ( first, second , third, forth ...etc) [message #191178 is a reply to message #190965] Tue, 05 September 2006 02:10 Go to previous messageGo to next message
rana.irfan@arzootex.com
Messages: 15
Registered: June 2006
Location: Faisalabad.
Junior Member

SQL> create or replace
function 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;

Function created.

SQL> select spell_number('1053550') from dual;

SPELL_NUMBER('1053550')
-------------------------------------------------------
One million Fifty-Three thousand Five Hundred Fifty

Regards,

Rana Irfan

Re: number to word ( first, second , third, forth ...etc) [message #191197 is a reply to message #191178] Tue, 05 September 2006 02:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nice. And looks very familiar
Please have the courtesy to show where you get your code from..
Re: number to word ( first, second , third, forth ...etc) [message #191201 is a reply to message #191197] Tue, 05 September 2006 03:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Frank wrote on Tue, 05 September 2006 09:52


Please have the courtesy to show where you get your code from..
Nod Exactly.

MHE
Re: number to word ( first, second , third, forth ...etc) [message #191208 is a reply to message #190965] Tue, 05 September 2006 03:38 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Since the OP was to spell ordinal numbers, here's a variation of Tom Kyte's code:
SQL> create or replace function spell_ordinal_number(p_number in number)
  2  return varchar2 
  3  as
  4    type myArray is table of varchar2(255);
  5    l_str myArray := myArray( '',
  6    ' thousand ', ' million ',
  7    ' billion ', ' trillion ',
  8    ' quadrillion ', ' quintillion ',
  9    ' sextillion ', ' septillion ',
 10    ' octillion ', ' nonillion ',
 11    ' decillion ', ' undecillion ',
 12    ' duodecillion ' );
 13    l_num varchar2(50) default trunc(p_number);
 14    l_return varchar2(4000);
 15  begin
 16    l_return := to_char(to_date(substr(l_num, length(l_num)-2, 3),'j' ),'jspth' ) || l_str(1) || l_return;
 17    l_num := substr( l_num, 1, length(l_num)-3 );
 18  
 19    for i in 2 .. l_str.count loop
 20      exit when l_num is null;
 21  
 22      if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then
 23        l_return := to_char(to_date(substr(l_num, length(l_num)-2, 3),'j' ),'jsp' ) || l_str(i) || l_return;
 24      end if;
 25      l_num := substr( l_num, 1, length(l_num)-3 );
 26    end loop;
 27    return l_return;
 28  end;
 29  /

Function created.

SQL> select spell_ordinal_number(5373485) from dual;

SPELL_ORDINAL_NUMBER(5373485)
------------------------------------------------------------------------
five million three hundred seventy-three thousand four hundred eighty-fifth
Previous Topic: Y is this not working..Urgent ..Plz
Next Topic: Problem in executing a procedure
Goto Forum:
  


Current Time: Tue Dec 06 10:36:03 CST 2016

Total time taken to generate the page: 0.16228 seconds