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;
or
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; /
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.
how i fetch my amount column in indian rupee format