Results 1 to 4 of 4

Thread: number format

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: number format

    Is there any built in formatting function that could convert

    2,123

    to

    "two thousand one hundred and twenty three"

    -cf

  2. #2
    Join Date
    May 2006
    Posts
    132
    Sure...check here Spelling Out a Number.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    My own version of a spell function that handles cents and MUCH bigger numbers follows.

    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
    Apr 2004
    Posts
    246
    fyi, the values in the array after "duodecillion" are:

    ' tredecillion ',
    ' quattuordecillion ',
    ' quindecillion ',
    ' sexdecillion ',
    ' septendecillion ',
    ' octodecillion ',
    ' novemdecillion ',
    ' vigintillion '
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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