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 Comments

nidhi maheshwari September 26, 2016

how i fetch my amount column in indian rupee format
V. Kapoor September 26, 2016

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.

Comments are closed.