Converting digits/numbers in words for INR currency (Indian Rupees) in Oracle PL/SQL

Below are the two functions for converting numbers/digits to Indian Rupees words. The main function is Rupees function.

Usage:

Select Rupees(99999.99) from dual;

Set serveroutput on;
Declare
  vstring varchar2(200);
Begin
   vstring := Rupees(9546);
  dbms_output.put_line(vstring);
end;

It is valid for maximum 99,999.00, you can further modify.

CREATE OR REPLACE FUNCTION number_to_word (pnum in number) RETURN varchar2 IS

rvalue varchar2(50);

BEGIN

  if pnum = 1 then     

      rvalue := 'One';

  elsif pnum = 2 then

      rvalue := 'Two';

  elsif pnum = 3 then

      rvalue := 'Three';

  elsif pnum = 4 then

      rvalue := 'Four';

  elsif pnum = 5 then

      rvalue := 'Five';

  elsif pnum = 6 then

      rvalue := 'Six';

  elsif pnum = 7 then

      rvalue := 'Seven';

  elsif pnum = 8 then

      rvalue := 'Eight';

  elsif pnum = 9 then 

      rvalue := 'Nine';

  elsif pnum = 10 then

      rvalue := 'Ten';

  elsif pnum = 11 then

      rvalue := 'Eleven';

  elsif pnum = 12 then

      Rvalue := 'Twelve';

  elsif pnum = 13 then

      rvalue := 'Thirteen';

  elsif pnum = 14 then

      rvalue := 'Fourteen';

  elsif pnum = 15 then

      rvalue := 'Fifteen';

  elsif pnum = 16 then

      rvalue := 'Sixteen';

  elsif pnum = 17 then

      rvalue := 'Seventeen';

  elsif pnum = 18 then

      rvalue := 'Eighteen';

  elsif pnum = 19 then

      rvalue := 'Nineteen';

  elsif pnum = 20 then

      rvalue := 'Twenty';

  elsif pnum = 30 then

      rvalue := 'Thirty';

  elsif pnum = 40 then

      rvalue := 'Forty';

  elsif pnum = 50 then

      rvalue := 'Fifty';

  elsif pnum = 60 then

      rvalue := 'Sixty';

  elsif pnum = 70 then

      rvalue := 'Seventy';

  elsif pnum = 80 then

      rvalue := 'Eighty';

  elsif pnum = 90 then

      rvalue := 'Ninety';

  else

      rvalue := '';

  end if;

  return(rvalue);

END;

/

CREATE OR REPLACE FUNCTION Rupees (pn IN NUMBER)

   RETURN VARCHAR2

IS

   vt     VARCHAR2 (200);

   ntv    VARCHAR2 (10);

   vlen   NUMBER (2);

BEGIN

   vt := 'Rupees ';

   ntv := RTRIM (LTRIM (TO_CHAR (pn)));

   vlen := LENGTH (ntv);

   IF vlen > 0 AND vlen < 2

   THEN                                                        -- single digit

      vt := vt || number_to_word (pn);

   ELSIF vlen > 1 AND vlen < 3

   THEN                                                           -- two digit

      IF pn < 21

      THEN

         vt := vt || number_to_word (pn);

      ELSE

         vt :=

            vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1) || '0'));

         vt := vt || ' ';

         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1)));

      END IF;

   -- two digit handled.

   ELSIF vlen > 2 AND vlen < 4

   THEN                                                         -- three digit

      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1)));

      vt := vt || ' Hundred ';

      IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 0

      THEN

         vt := vt || 'and ';

      END IF;

      IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 20

      THEN

         vt :=

            vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1) || '0'));

         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0

         THEN

            vt := vt || ' ';

         END IF;

         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1)));

      ELSE

         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 2)));

         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0

         THEN

            vt := vt || ' ';

         END IF;

      END IF;

   -- three digit handled

   ELSIF vlen > 3 AND vlen < 5

   THEN                                                            -- thousand

      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1)));

      vt := vt || ' Thousand ';

      IF TO_NUMBER (SUBSTR (ntv, 2, 3)) > 0

      THEN

         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0

         THEN

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1)));

            vt := vt || ' Hundred ';

         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 0

         THEN

            vt := vt || 'and ';

         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 20

         THEN

            -- 9999

            vt :=

               vt

               || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1) || '0'));

            IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0

            THEN

               vt := vt || ' ';

            END IF;

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1)));

         ELSE

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 2)));

            IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0

            THEN

               vt := vt || ' ';

            END IF;

         END IF;

      END IF;

      -- thousand handled now 99 thousand.

      ELSIF vlen > 4 AND vlen < 6

   THEN                                                            

   -- thousand

   if to_number(substr(ntv,1,2)) <= 20 then

      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 2)));

      vt := vt || ' Thousand ';

      else

      vt := vt || number_to_word(to_number(substr(ntv,1,1)||'0'));

      vt := vt || ' '|| number_to_word(to_number(substr(ntv,2,1)));

      vt := vt || ' Thousand ';

   end if;

      IF TO_NUMBER (SUBSTR (ntv, 3, 3)) > 0

      THEN

         IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0

         THEN

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1)));

            vt := vt || ' Hundred ';

         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 4, 2)) > 0

         THEN

            vt := vt || 'and ';

         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 4, 2)) > 20

         THEN

            -- 99999

            vt :=

               vt

               || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1) || '0'));

            IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0

            THEN

               vt := vt || ' ';

            END IF;

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 5, 1)));

         ELSE

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 2)));

            IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0

            THEN

               vt := vt || ' ';

            END IF;

         END IF;

      END IF;

   END IF;

   IF LENGTH (vt) > 7

   THEN

      vt := RTRIM (vt) || ' only.';

   ELSE

      vt := '';

   END IF;

   RETURN (vt);

END;

/

2 thoughts on “Converting digits/numbers in words for INR currency (Indian Rupees) in Oracle PL/SQL”

V. Kapoor
September 26, 2016 at 9:56 am
Create both functions (number_to_word and rupees) in your schema then call the rupees functions by passing any number to return string containing words. I already given the examples in the top of this post.
nidhi maheshwari
September 26, 2016 at 9:52 am
how i fetch my amount column in indian rupee format

Comments are closed.