10 Replies Latest reply on Dec 5, 2008 7:29 AM by Karthick2003

    Convert Number into word.....

    589132
      Hi All,

      I am using following query to convery number into word and it is working fine.

      SELECT TO_CHAR(TO_DATE(100,'J'),'JSP') FROM DUAL

      but if i want to view the values after decimal i am getting error. Please refer below query

      SELECT TO_CHAR(TO_DATE(100.50,'J'),'JSP') FROM DUAL

      Can anyone help me out.?

      Thanks,
        • 1. Re: Convert Number into word.....
          William Robertson
          Is the .50 "and fifty pence" (i.e. currency) or is it "point five zero"? The first one will be easier
          • 2. Re: Convert Number into word.....
            589132
            Hi William
            It is point five Zero ,
            It is used as currency column , i want to show in word format..

            Thank from you reply...
            • 3. Re: Convert Number into word.....
              Karthick2003
              Got this from asktom.
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              
              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
               23          if ( substr(l_num, length(l_num)-2, 3) <> 0 )
               24          then
               25              l_return := to_char(
               26                              to_date(
               27                               substr(l_num, length(l_num)-2, 3),
               28                                 'J' ),
               29                          'Jsp' ) || l_str(i) || l_return;
               30          end if;
               31          l_num := substr( l_num, 1, length(l_num)-3 );
               32      end loop;
               33
               34      -- beginning of section added to include decimal places:
               35      if to_char( p_number ) like '%.%'
               36      then
               37          l_num := substr( p_number, instr( p_number, '.' )+1 );
               38          if l_num > 0
               39          then
               40              l_return := l_return || ' point';
               41              for i in 1 .. length (l_num)
               42              loop
               43                  exit when l_num is null;
               44                  if substr( l_num, 1, 1 ) = '0'
               45                  then
               46                      l_return := l_return || ' zero';
               47                  else
               48                      l_return := l_return
               49                      || ' '
               50                      || to_char(
               51                             to_date(
               52                             substr( l_num, 1, 1),
               53                               'j' ),
               54                         'jsp' );
               55                  end if;
               56                  l_num := substr( l_num, 2 );
               57              end loop;
               58          end if;
               59      end if;
               60      -- end of section added to include decimal places
               61
               62      return l_return;
               63  end spell_number;
               64  /
              
              Function created.
              
              SQL> select spell_number('99.999') from dual
                2  /
              
              SPELL_NUMBER('99.999')
              --------------------------------------------------------------------------------
              Ninety-Nine point nine nine nine
              
              SQL> select spell_number('9979689769796897976979869.967859999') from dual
                2  /
              
              SPELL_NUMBER('9979689769796897976979869.967859999')
              --------------------------------------------------------------------------------
              Nine septillion Nine Hundred Seventy-Nine sextillion Six Hundred Eighty-Nine qui
              ntillion Seven Hundred Sixty-Nine quadrillion Seven Hundred Ninety-Six trillion
              Eight Hundred Ninety-Seven billion Nine Hundred Seventy-Six million Nine Hundred
               Seventy-Nine thousand Eight Hundred Sixty-Nine point nine six seven eight five
              nine nine nine nine
              For more info check this link.

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650

              Thanks,
              Karthick.

              Message was edited by:
              karthick_arp
              • 4. Re: Convert Number into word.....
                William Robertson
                If it is "point five zero" you'll have to separate out each digit after the decimal point and convert each one with a separate TO_CHAR(TO_DATE(...,'J','JSP')) expression. If you can use "and fifty cents" then you can just use something like TRUNC(MOD(the_amount,1) *100) to make the decimal fraction into a whole number from 0 to 99, and convert that number into words.
                • 5. Re: Convert Number into word.....
                  Karthick2003
                  Its all there done before!!! only pain we have to take is to search for it :-) and asktom is a great place to search. I get most of my answers there!!!
                  • 6. Re: Convert Number into word.....
                    William Robertson
                    I always thought "septillion" was a bit over the top ;)
                    • 7. Re: Convert Number into word.....
                      589132
                      Hi Team
                      i got very nice idea to user decode ..

                      select decode(trunc(54334.69),0,'ZERO',to_char(to_date(trunc(54334.69),'J'),'JSP'))
                      || ' DOLLARS AND ' || decode(trunc(mod(54334.69,1)*100),0,'ZERO',
                      to_char(to_date(trunc(mod(54334.69,1)*100),'J'),'JSP')) || ' CENTS'
                      from dual;

                      Column
                      ----------------------------------------------------------------------------------------------------
                      FIFTY-FOUR THOUSAND THREE HUNDRED THIRTY-FOUR DOLLARS AND SIXTY-NINE CENTS

                      Thanks all
                      • 8. Re: Convert Number into word.....
                        Sven W.
                        Nice idea. What if we deal with millions of dollars? Or if you program something for Mr. Lakshmi Mittal, then lets talk about several billions.

                        Please give your decode version and compare it to what already has been suggested.
                        • 9. Re: Convert Number into word.....
                          R
                          Hi Karthi can u send me the code to convert an amount of 54356786.75 in Indian Currency format and last it should display 75 paisa



                          Regards,
                          RR
                          • 10. Re: Convert Number into word.....
                            Karthick2003
                            You mean like this...
                            SQL> create or replace function spell_number_inr( p_number in varchar2 )
                              2  return varchar2
                              3  -- original by Tom Kyte
                              4  -- modified to include decimal places
                              5  as
                              6      type myArray is table of varchar2(255);
                              7      l_str    myArray := myArray( '',
                              8                             ' thousand ', ' million ',
                              9                             ' billion ', ' trillion ',
                             10                             ' quadrillion ', ' quintillion ',
                             11                             ' sextillion ', ' septillion ',
                             12                             ' octillion ', ' nonillion ',
                             13                             ' decillion ', ' undecillion ',
                             14                             ' duodecillion ' );
                             15      l_num varchar2(50) default trunc( p_number );
                             16      l_return varchar2(4000);
                             17  begin
                             18      for i in 1 .. l_str.count
                             19      loop
                             20          exit when l_num is null;
                             21
                             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
                             33      l_return := l_return || ' Rupees';
                             34
                             35      -- beginning of section added to include decimal places:
                             36      if p_number like '%.%'
                             37      then
                             38             l_return := l_return || ' and';
                             39          l_num := substr( p_number, instr( p_number, '.' )+1 );
                             40          l_return := l_return
                             41                         || ' '
                             42                         || to_char(
                             43                                to_date(l_num,'j' ),
                             44                         'jsp' );
                             45
                             46             l_return := l_return || ' Paisa';
                             47      end if;
                             48      -- end of section added to include decimal places
                             49
                             50      return l_return;
                             51  end spell_number_inr;
                             52  /
                            
                            Function created.
                            
                            SQL> select spell_number_inr(99.99) from dual
                              2  /
                            
                            SPELL_NUMBER_INR(99.99)
                            ----------------------------------------------------------------------------------------------------
                            Ninety-Nine Rupees and ninety-nine Paisa
                            
                            SQL> select spell_number_inr(100) from dual
                              2  /
                            
                            SPELL_NUMBER_INR(100)
                            ----------------------------------------------------------------------------------------------------
                            One Hundred Rupees
                            
                            SQL> select spell_number_inr(100.45) from dual
                              2  /
                            
                            SPELL_NUMBER_INR(100.45)
                            ----------------------------------------------------------------------------------------------------
                            One Hundred Rupees and forty-five Paisa
                            
                            SQL> select spell_number_inr(343300.75) from dual
                              2  /
                            
                            SPELL_NUMBER_INR(343300.75)
                            ----------------------------------------------------------------------------------------------------
                            Three Hundred Forty-Three thousand Three Hundred Rupees and seventy-five Paisa
                            And one thing don't re invoke old posts.

                            Thanks,
                            Karthick.