PL/SQL function can be used to convert the entered ruppies( in numbers) into words

PL/SQL function can be used to convert the entered ruppies( in numbers) into words

 

Following PL/SQL function can be used to convert the entered ruppies( in numbers) into words format. 

So if we provide 123456 as an input to the function it will give return the output as "One lac twnety three thousand four hundred fifty six"

Input given to Function: 123456

Output from Function:  One Lac Twnety Three Thousand Four Hundred Fifty Six Only

Function Script:

CREATE OR REPLACE FUNCTION ruppees_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 ruppees_to_word;

TEST SCRIPT:

SET serveroutput on;

BEGIN

   DBMS_OUTPUT.put_line (ruppees_to_word (123456));

END;