This discussion is archived
10 Replies Latest reply: Jan 11, 2013 7:32 AM by BluShadow RSS

better query

user in Explorer
Currently Being Moderated
i want convert a digit to words
i have written the following code
can i simplify this code
create or replace FUNCTION fun_ntow
                      (
                        Digit IN NUMBER
                      )
   RETURN VARCHAR2
   IS

      TYPE vmWord IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
      mWord vmWord;
      digword Varchar2(200);
      decword Varchar2(200);
      mwords  Varchar2(200);
      mxctr Number;
      mnum Number;
      mlen Number;
      mint number;
      mmod number;

   BEGIN

      mword(1) := 'One';
      mword(2) := 'Two';
      mword(3) := 'Three';
      mword(4) := 'Four';
      mword(5) := 'Five';
      mword(6) := 'Six';
      mword(7) := 'Seven';
      mword(8) := 'Eight';
      mword(9) := 'Nine';
      mword(10):= 'Ten';
      mword(11) := 'Eleven';
      mword(12) := 'Twelve';
      mword(13) := 'Thirteen';
      mword(14) := 'Fourteen';
      mword(15) := 'Fifteen';
      mword(16) := 'Sixteen';
      mword(17) := 'Seventeen';
      mword(18) := 'Eighteen';
      mword(19) := 'Nineteen';
      mword(20) := 'Twenty';
      mword(21) := 'Thirty';
      mword(22) := 'Fourty';
      mword(23) := 'Fifty';
      mword(24) := 'Sixty';
      mword(25) := 'Seventy';
      mword(26) := 'Eighty';
      mword(27) := 'Ninety';
      mword(28) := 'Crore';
      mword(29) := 'Lakh';
      mword(30) := 'Thousand';
      mword(31) := 'Hundred';
      mword(32) := '';
      mword(33) := '';
      mword(34) := '';
      mwords := '';
      mxctr := 28;


      --digword :=  TO_CHAR(digit,'00000000.00') ;

      digword :=  TO_CHAR(digit,'00000000.00') ;
      decword := To_Char(digit, '.00');
      mlen := 1;

      WHILE mlen <= 12
      LOOP

         SELECT TO_NUMBER(SUBSTR(digword,mlen,decode(mlen,7,1,2)))
         INTO   mnum
         FROM   dual;

         IF mnum > 0
         THEN

        IF mlen = 11
            THEN
               mwords := mwords || 'Point ';
            END IF; --mlen = 11

            IF mnum <= 20 AND mnum > 0
        THEN
               mwords := mwords || ' ' || mword(mnum);
            ELSE
               SELECT TRUNC(mnum / 10)
           INTO   mint
           FROM   DUAL;

           SELECT MOD(mnum, 10)
           INTO   mmod
           FROM   DUAL;

               mwords := mwords || mword(20 + mint - 2);

           IF  mmod <> 0
           THEN
                  mwords := mwords || ' ' || mword(mmod);
               END IF;

            END IF; --mnum <= 20 And mnum > 0

        IF mlen = 11
        THEN
               mwords := mwords || ' ';
            END IF;

            mwords := mwords || ' ' || mword(mxctr) || ' ' ;

         END IF; --mnum > 0

         mxctr := mxctr + 1;

         IF mlen = 8
         THEN
            mlen := mlen + 3;
         ELSIF mlen <> 7
         THEN
            mlen := mlen + 2;
         ELSE
            mlen := mlen + 1;
         END IF;

      END LOOP; --WHILE mlen <= 12

      RETURN LOWER(mwords);
    END fun_ntow;
  • 1. Re: better query
    BluShadow Guru Moderator
    Currently Being Moderated
    How large are the numbers you want to convert.

    There's a way it can be done simply using date functionality....
    SQL> select to_char(to_date(12345,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(12345,'J'),'JSP')
    ----------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    though this is limited to the range of values you can use.
    SQL> select to_char(to_date(1234567,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(1234567,'J'),'JSP')
    ---------------------------------------------------------------------
    ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN
    
    SQL> select to_char(to_date(12345678,'J'),'JSP') from dual;
    select to_char(to_date(12345678,'J'),'JSP') from dual
                           *
    ERROR at line 1:
    ORA-01830: date format picture ends before converting entire input string
  • 2. Re: better query
    user in Explorer
    Currently Being Moderated
    max is upto 99 crores
    this is in Indian rupees
  • 3. Re: better query
    BluShadow Guru Moderator
    Currently Being Moderated
    What is a crore?

    This is an international forum and we use international terms here, so please provide the answer such as "9 million" or suchlike.
  • 4. Re: better query
    user in Explorer
    Currently Being Moderated
    around 90 million
  • 5. Re: better query
    jeneesh Guru
    Currently Being Moderated
    Read it form AskTom Spell The number
  • 6. Re: better query
    user in Explorer
    Currently Being Moderated
    select to_char(to_date(12345,'J'),'JSP') from dual;
     
    TO_CHAR(TO_DATE(12345,'J'),'JSP')
    ----------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    how does this query work
    i am really not able to understand
    please help

    Edited by: user in on Jan 11, 2013 4:40 PM
  • 7. Re: better query
    BluShadow Guru Moderator
    Currently Being Moderated
    user in wrote:
    select to_char(to_date(12345,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(12345,'J'),'JSP')
    ----------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    how does this query work
    i am really not able to understand
    please help
    It takes the number and treats it as a Julian date (rather than gregorian calendar. Julian dates are a number of days since 0 January 4712, so 1 is the 1st Jan 4712) by turning it into a date with the TO_DATE function and the "J" format.
    SQL> select to_date(1,'J') from dual;
    
    TO_DATE(1,'J')
    --------------------
    01-JAN-4712 00:00:00
    
    SQL> select to_date(2,'J') from dual;
    
    TO_DATE(2,'J')
    --------------------
    02-JAN-4712 00:00:00
    
    SQL> select to_date(12345,'J') from dual;
    
    TO_DATE(12345,'J')
    --------------------
    19-OCT-4679 00:00:00
    We then take the date and convert it back to a string using the TO_CHAR. If we did this with the same "J" format we'd get the same number of days since 0 Jan 4712 back out...
    SQL> select to_char(to_date(12345,'J'),'J') from dual;
    
    TO_CHAR
    -------
    0012345
    But TO_CHAR also includes another format called "JSP" which is the "Julian Spelling" format, i.e. the numbers are spelt out in words.
    SQL> select to_char(to_date(12345,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(12345,'J'),'JSP')
    ----------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    However, because it's using DATE functionality, the number being used is limited by what date's Oracle will deal with and therefore if you specify an number that is <1 or too big for the Julian dates you'll get...
    ORA-01854: julian date must be between 1 and 5373484
    So, that's the limit of numbers you can use.
    SQL> select to_char(to_date(5373484,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(5373484,'J'),'JSP')
    --------------------------------------------------------------------------
    FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
    If you need to deal with numbers larger than that then you have to manually implement it as described already in other threads such as Tom's.
  • 8. Re: better query
    BluShadow Guru Moderator
    Currently Being Moderated
    To add...

    It's easy to get confused too. Don't think that the year 4712 in the Julian calander is the equivalent of year 4712 in the current gregorian calendar. They're completely different years.
    SQL> ed
    Wrote file afiedt.buf
    
      1  select to_date(1,'J') as julian_dt
      2        ,dump(to_date(1,'J')) as julian_dump
      3        ,to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt
      4        ,dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump
      5* from dual
    SQL> /
    
    JULIAN_DT            JULIAN_DUMP                       GREGORIAN_DT         GREGORIAN_DUMP
    -------------------- --------------------------------- -------------------- --------------------------------
    01-JAN-4712 00:00:00 Typ=13 Len=8: 152,237,1,1,0,0,0,0 01-JAN-4712 00:00:00 Typ=13 Len=8: 104,18,1,1,0,0,0,0
    http://en.wikipedia.org/wiki/Julian_calendar

    Edited by: BluShadow on 11-Jan-2013 11:30
    made example clearer
  • 9. Re: better query
    John Spencer Oracle ACE
    Currently Being Moderated
    BluShadow wrote:
    To add...

    It's easy to get confused too. Don't think that the year 4712 in the Julian calander is the equivalent of year 4712 in the current gregorian calendar. They're completely different years.
    SQL> ed
    Wrote file afiedt.buf
    
    1  select to_date(1,'J') as julian_dt
    2        ,dump(to_date(1,'J')) as julian_dump
    3        ,to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt
    4        ,dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump
    5* from dual
    SQL> /
    
    JULIAN_DT            JULIAN_DUMP                       GREGORIAN_DT         GREGORIAN_DUMP
    -------------------- --------------------------------- -------------------- --------------------------------
    01-JAN-4712 00:00:00 Typ=13 Len=8: 152,237,1,1,0,0,0,0 01-JAN-4712 00:00:00 Typ=13 Len=8: 104,18,1,1,0,0,0,0
    http://en.wikipedia.org/wiki/Julian_calendar

    Edited by: BluShadow on 11-Jan-2013 11:30
    made example clearer
    Blu:
    Are you sure they are diferent years? Julian date 1 is 4712 BCE while (unqualified) 4712 is 4712 CE.
    SQL> alter session set nls_date_format = 'dd-mon-yyyy AD';
    
    Session altered.
    
    SQL> select to_date(1,'J') as julian_dt,
      2         dump(to_date(1,'J')) as julian_dump,
      3         to_date('01-JAN-4712','DD-MON-YYYY') as gregorian_dt,
      4         dump(to_date('01-JAN-4712','DD-MON-YYYY')) as gregorian_dump,
      5         to_date('01-JAN-4712 BC','DD-MON-YYYY BC') as gregorian_dt_bce,
      6         dump(to_date('01-JAN-4712 BC','DD-MON-YYYY BC')) as gregorian_dump_bce
      7  from dual;
    
    JULIAN_DT      JULIAN_DUMP                       GREGORIAN_DT   GREGORIAN_DUMP                   GREGORIAN_DT_B GREGORIAN_DUMP_BCE
    -------------- --------------------------------- -------------- -------------------------------- -------------- ---------------------------------
    01-jan-4712 BC Typ=13 Len=8: 237,152,1,1,0,0,0,0 01-jan-4712 AD Typ=13 Len=8: 18,104,1,1,0,0,0,0 01-jan-4712 BC Typ=13 Len=8: 237,152,1,1,0,0,0,0
    John
  • 10. Re: better query
    BluShadow Guru Moderator
    Currently Being Moderated
    John Spencer wrote:
    Blu:
    Are you sure they are diferent years? Julian date 1 is 4712 BCE while (unqualified) 4712 is 4712 CE.
    You're correct, but how many people are going to be looking at the BCE and CE parts of dates in their output format.
    I missed that myself admittedly, but that just indicates how easily we can be confused into seeing a year of 4712 and thinking they may be the same... or different... or whatever. hehe.

    Anyway... point well made... it can be confusing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points