PL/SQL Function To Convert Ruppies(Numbers) In 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;

Other Most Useful PLSQL Scripts:

1. PL/SQL Function To Compute The Factorial Of A Number
2. PL/SQL Function To Convert A Binary Number To A Decimal Number
3. PL/SQL Function To Convert A Decimal Number To A Binary Number
4. PL/SQL Function To Convert Ruppies(Numbers) In Words
5. PL/SQL Function To Generate The Fibonacci Series
6. PL/SQL Procedure For Counting All Tables And Respective Rows From Database
7. PL/SQL Procedure To Display Monthly Calender
8. PL/SQL Procedure To Reverse A String
9. PL/SQL Script To Calculate Weekdays Between Two Given Dates