Home » SQL & PL/SQL » SQL & PL/SQL » Amount in words
Amount in words [message #416735] Tue, 04 August 2009 02:28 Go to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Dear All,

I am using below code to spell number in words


create or replace
   function spell_number( p_number in number )
   return varchar2
   -- original by Tom Kyte
   -- modified to include decimal places
   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;
       -- 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 l_num > 0
           then
               l_return := l_return || ' And';
               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;
       end if;
       -- end of section added to include decimal places
       return l_return;
  end spell_number;
/



The number amount is : 9899.25
I am getting the output is:

 NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE




But i want output should be

NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY


Please suggest where i need to modify the code.

Files is equal to cent as per DOLLOR currency
Thanks
Re: Amount in words [message #416737 is a reply to message #416735] Tue, 04 August 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace
  2     function spell_number( p_number in number )
  3     return varchar2
  4     -- original by Tom Kyte
  5     -- modified to include decimal places
  6     as
  7         type myArray is table of varchar2(255);
  8         l_str    myArray := myArray( '',
  9                                ' thousand ', ' million ',
 10                                ' billion ', ' trillion ',
 11                                ' quadrillion ', ' quintillion ',
 12                                ' sextillion ', ' septillion ',
 13                                ' octillion ', ' nonillion ',
 14                                ' decillion ', ' undecillion ',
 15                                ' duodecillion ' );
 16         l_num varchar2(50) default trunc( p_number );
 17         l_return varchar2(4000);
 18     begin
 19         for i in 1 .. l_str.count
 20         loop
 21             exit when l_num is null;
 22             if ( substr(l_num, length(l_num)-2, 3) <> 0 )
 23             then
 24                 l_return := to_char(
 25                                 to_date(
 26                                  substr(l_num, length(l_num)-2, 3),
 27                                    'J' ),
 28                             'Jsp' ) || l_str(i) || l_return;
 29             end if;
 30             l_num := substr( l_num, 1, length(l_num)-3 );
 31         end loop;
 32         -- beginning of section added to include decimal places:
 33         if to_char( p_number ) like '%.%'
 34         then
 35             l_num := substr( p_number, instr( p_number, '.' )+1 );
 36             if l_num > 0
 37             then
 38                 l_return := l_return || ' And';
 39                 l_return := l_return || ' '
 40                             || to_char(to_date(l_num, 'j'), 'jsp');
 41                 l_return := l_return || ' FILS ONLY';
 42             end if;
 43         end if;
 44         -- end of section added to include decimal places
 45         return l_return;
 46    end spell_number;
 47  /

Function created.

SQL> 
SQL> select spell_number(9899.25) from dual;
SPELL_NUMBER(9899.25)
---------------------------------------------------------------------------------
Nine thousand Eight Hundred Ninety-Nine And twenty-five FILS ONLY

1 row selected.

Regards
Michel
Re: Amount in words [message #472477 is a reply to message #416737] Fri, 20 August 2010 22:02 Go to previous messageGo to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Hi,

When i am writing like this :

select spell_number(1.50) from dual then it is coming like One point five.

I want to show like One point fifty.

Please update me.

Thanks

Samit Gandhi
Re: Amount in words [message #472480 is a reply to message #472477] Sat, 21 August 2010 00:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
The following assumes that you want two decimals places only.

create or replace
   function spell_number( p_number in number )
   return varchar2
   -- original by Tom Kyte
   -- modified to include two decimal places
   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;
       -- beginning of section added to include two decimal places:
       if to_char( p_number ) like '%.%'
       then
           l_num := substr( p_number, instr( p_number, '.' )+1 );
           if l_num > 0
           then
               l_num := rpad (substr (l_num, 1, 2), 2, '0');
               l_return := l_return || ' point '
                           || to_char(to_date(l_num, 'j'), 'jsp');
           end if;
       end if;
       -- end of section added to include two decimal places
       return l_return;
  end spell_number;
/


SCOTT@orcl_11gR2> select spell_number (1.50) from dual
  2  /

SPELL_NUMBER(1.50)
--------------------------------------------------------------------------------
One point fifty

1 row selected.

SCOTT@orcl_11gR2>




Re: Amount in words [message #472514 is a reply to message #472480] Sat, 21 August 2010 06:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara,

there is a better way of spelling numbers - timestamp. Timestamp fractionanl part to be precise. It allows spelling up to 9 digit numbers (9 digits whole part and 9 digits fractional part):

variable amount number;
exec :amount := -9899.25;
select  :amount amount,
        decode(
               sign(:amount),
               -1,'minus '
              ) ||
        to_char(to_timestamp(lpad(trunc(abs(:amount)),9,'0'),'FF9'),'ffsp') ||
        decode(
               nvl(mod(abs(:amount),1),0),
               0,null,
                 ' point ' || to_char(to_timestamp(lpad(trunc(mod(abs(:amount),1)*100),9,'0'),'FF9'),'ffsp')
              ) spelled_amount
  from  dual
/

    AMOUNT SPELLED_AMOUNT
---------- ----------------------------------------------------------------------
  -9899.25 minus nine thousand eight hundred ninety-nine point twenty-five

SQL> 


SY.

[Updated on: Sat, 21 August 2010 06:33]

Report message to a moderator

Re: Amount in words [message #472516 is a reply to message #472514] Sat, 21 August 2010 08:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
For numbers less than 1,000,000,000 that looks nifty. You could put that in a function for convenience, rather than code it each time. If only using the last two decimal places, it is slightly less code to use to_date and j instead of to_timestamp and ff9 for the decimal portion, eliminating the need to lpad to nine zeroes.

create or replace function spell_amount
  (p_amount in number)
  return       varchar2
as
  v_amount     varchar2 (4000);
begin
  select  decode(
                 sign(p_amount),
                 -1,'minus '
                ) ||
          to_char(to_timestamp(lpad(trunc(abs(p_amount)),9,'0'),'FF9'),'ffsp') ||
          decode(
                 nvl(mod(abs(p_amount),1),0),
                 0,null,
                   ' point ' || to_char(to_date(trunc(mod(abs(p_amount),1)*100),'j'),'jsp')
                ) spelled_amount
  into    v_amount
  from  dual;
  return v_amount;
end spell_amount;
/


SCOTT@orcl_11gR2> variable amount number;
SCOTT@orcl_11gR2> exec :amount := -9899.2566;

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select :amount, spell_amount (:amount) from dual
  2  /

   :AMOUNT
----------
SPELL_AMOUNT(:AMOUNT)
--------------------------------------------------------------------------------
-9899.2566
minus nine thousand eight hundred ninety-nine point twenty-five


1 row selected.

SCOTT@orcl_11gR2>

Re: Amount in words [message #472520 is a reply to message #472516] Sat, 21 August 2010 09:18 Go to previous message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then I'd change DECODE to CASE and get rid of SELECT:

create or replace
  function spell_amount(
                        p_amount in number
                       )
    return       varchar2
    as
    begin
        return case sign(p_amount) when -1 then 'minus ' end ||
               to_char(to_timestamp(lpad(trunc(abs(p_amount)),9,'0'),'FF9'),'ffsp') ||
               case  nvl(mod(abs(p_amount),1),0)
                 when 0 then null
                 else ' point ' || to_char(to_timestamp(lpad(trunc(mod(abs(p_amount),1)*100),9,'0'),'FF9'),'ffsp')
               end;
end;
/


SY.
P.S. And by checking NLS_NUMERIC_CHARACTERS we could control point versus comma.
Previous Topic: function
Next Topic: Storing and reading xml data from an XMLType table
Goto Forum:
  


Current Time: Fri Dec 02 18:42:39 CST 2016

Total time taken to generate the page: 0.31296 seconds