Friday, July 8, 2011

Function for displaying Rupees in Words


Yesterday, my friend gave a PLSQL FUNCTION script for converting rupees in words.  It is very useful for me... hence shared the same to my audiences. Hope it helps everybody.

Input to Function: 1455555
Output from Function: One Million Four Hundred Fifty Five Thousand Five Hundred Fifty Five Only

Function Script:

CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER)
   RETURN VARCHAR2
AS
   v_length   INTEGER         := 0;
   v_num2     VARCHAR2 (50)   := NULL;
   v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));
   v_word     VARCHAR2 (4000) := NULL;
   v_word1    VARCHAR2 (4000) := NULL;

   TYPE myarray IS TABLE OF VARCHAR2 (255);

   v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
   IF ((amount = 0) OR (amount IS NULL))
   THEN
      v_word := 'zero';
   ELSIF (TO_CHAR (amount) LIKE '%.%')
   THEN
      IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
      THEN
         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);

         IF (LENGTH (v_num2) < 2)
         THEN
            v_num2 := v_num2 * 10;
         END IF;

         v_word1 :=
               ' AND '
            || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2),
                                  'J'),
                         'JSP'
                        )
               )
            || ' paise ';
         v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
         v_word :=
               TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || v_word;
         v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);

         FOR i IN 1 .. v_str.COUNT
         LOOP
            EXIT WHEN (v_amount IS NULL);
            v_word :=
                  TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
                                            2),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || v_str (i)
               || v_word;
            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
         END LOOP;
      END IF;
   ELSE
      v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
   END IF;

   v_word := v_word || ' ' || v_word1 || ' only ';
   v_word := REPLACE (RTRIM (v_word), ' ', ' ');
   v_word := REPLACE (RTRIM (v_word), '-', ' ');
   RETURN INITCAP (v_word);
END ruppee_to_word;

TEST SCRIPT:

SET serveroutput on;

BEGIN
   DBMS_OUTPUT.put_line (ruppee_to_word (1455555));
END;


4 Responses to “Function for displaying Rupees in Words”

Hire Joomla Programmers said...
October 7, 2011 at 3:08 AM

Is it work in Hindi Keyboard?


Anonymous said...
February 13, 2017 at 11:18 PM

select to_char(to_date(145688,'j'),'Jsp') in_words from dual;

it will give the same output...


Anonymous said...
June 15, 2017 at 5:47 AM

It is working in Indian style when the amt is in fractions and if not in fractions returning in UK/US fashion.


Imran Faisal said...
September 1, 2021 at 5:09 AM

Not working for amount 1000.15, giving error
ORA-01854: julian date must be between 1 and 5373484
01854. 00000 - "julian date must be between 1 and 5373484"
*Cause: An invalid Julian date was entered.
*Action: Enter a valid Julian date between 1 and 5373484.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.