Converting digits/numbers in words for INR currency (Indian Rupees) in Oracle PL/SQL
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; /
how i fetch my amount column in indian rupee format
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.