Friday, July 8, 2011
Function for displaying Rupees in Words
Yesterday, my friend gave a PLSQL FUNCTION script for converting rupees in words. It is very useful for me... hence shared the same to my audiences. Hope it helps everybody.
Input to Function: 1455555
Output from Function: One Million Four Hundred Fifty Five Thousand Five Hundred Fifty Five Only
Function Script:
CREATE OR REPLACE FUNCTION ruppee_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 ruppee_to_word;
TEST SCRIPT:
SET serveroutput on;
BEGIN
DBMS_OUTPUT.put_line (ruppee_to_word (1455555));
END;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
4 Responses to “Function for displaying Rupees in Words”
October 7, 2011 at 3:08 AM
Is it work in Hindi Keyboard?
February 13, 2017 at 11:18 PM
select to_char(to_date(145688,'j'),'Jsp') in_words from dual;
it will give the same output...
June 15, 2017 at 5:47 AM
It is working in Indian style when the amt is in fractions and if not in fractions returning in UK/US fashion.
September 1, 2021 at 5:09 AM
Not working for amount 1000.15, giving error
ORA-01854: julian date must be between 1 and 5373484
01854. 00000 - "julian date must be between 1 and 5373484"
*Cause: An invalid Julian date was entered.
*Action: Enter a valid Julian date between 1 and 5373484.
Post a Comment