Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Question Unanswered: Number to equivalent text

    Hi guys

    Happy NewYear!!!

    How we can convert a number to equivalent text the easiest way using pl/sql functions? I mean, say I have a number 1225.75 and I want it to displayed as "One thousand two hundred twenty five & 75/100"

    I am very new to Oracle, so any sort of suggestions or sample coding will be highly appreciated.

    Thanks in advance.

    raj
    If it is a door, It will open.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    With a litle modification to the J->JSP trick you can do this
    Code:
    SQL@8i> create or replace function spell ( p_number in number ) return varchar2
      2  is
      3     l_integer number := trunc( p_number );
      4     l_decimal number := p_number - l_integer;
      5  begin
      6     if l_decimal > 0
      7     then
      8             return to_char( to_date( l_integer,'J' ),'JSP' ) || ' & ' || trunc( l_decimal,2 )*100 || '/100';
      9     else
     10             return to_char( to_date( l_integer,'J' ),'JSP' );
     11     end if;
     12  end;
     13  /
    
    Function created.
    
    SQL@8i> select spell( 1225.75 ) from dual;
    
    SPELL(1225.75)
    ----------------------------------------------------------------------------------------------------
    ONE THOUSAND TWO HUNDRED TWENTY-FIVE & 75/100
    
    SQL@8i>
    Last edited by JMartinez; 01-03-05 at 10:07.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Here is a script that I use. p_format is equal to 'D' or 'N'. 'D'=dollar format, 'N'=numeric. Feel free to modify the routine, I modified it from someone else.

    Code:
    function spell_number( p_number in number,
                           p_format in varchar2)
    return varchar2
    is
    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);
     x_point varchar2(20);
     begin
         if upper(p_format) = 'D' then
           x_point := ' Dollars and';
         else
           x_point := ' point';
         end if;
         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;
     
         -- beginning of section added to include decimal places:
         if to_char( p_number ) like '%.%'
         then
             l_num := substr( p_number, instr( p_number, '.' )+1 );
             if p_format = 'D' then
                if l_num > 99 then
                  return null;
                elsif length(l_num) < 2 then
                  l_num := l_num||'0';
                end if;
                l_return := l_return||' Dollars and '||l_num||' Cents';
                return l_return;
             end if;      
             if l_num > 0
             then
                l_return := l_return || x_point;
                for i in 1 .. length (l_num)
                loop
                     exit when l_num is null;
                     if substr( l_num, 1, 1 ) = '0'
                     then
                         l_return := l_return || ' zero';
                     else
                         l_return := l_return
                         || ' '
                         || to_char(
                                to_date(
                                substr( l_num, 1, 1),
                                  'j' ),
                            'jsp' );
                     end if;
                     l_num := substr( l_num, 2 );
                 end loop;
             end if;
         else
           if p_format = 'D' then
             l_return := l_return||' Dollars and 00 Cents';
           end if;
         end if;
         -- end of section added to include decimal places
     
         return l_return;
    
     end spell_number;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Smile Thank you guys!!!

    I really appreciate both of you for the samples. I think my work will be definitely done using one of the functions provided.

    Thanks again

    raj
    If it is a door, It will open.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •