Archive

Archive for the ‘Convert Number to Word’ Category

Convert Number to Word

September 6, 2010 Leave a comment
SQL> select to_char(to_date(5358452,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(5358452,'J'),'JSP')
----------------------------------------------------------------------
FIVE MILLION THREE HUNDRED FIFTY-EIGHT THOUSAND FOUR HUNDRED FIFTY-TWO

Restriction: It can conversion from 1 to 5373484 and it can not conversion decimal number.

A function for convert number to word

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 ',
                              ' dont read '   , ' 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;
 /

Example

SQL> Select spell_number( 1234567899 ) From dual;

SPELL_NUMBER(1234567899)
------------------------
One billion Two Hundred Thirty-Four million 
Five Hundred Sixty-Seven thousand Eight Hundred 
Ninety-Nine
Advertisements