Friday, July 8, 2011
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;
Friday, July 8, 2011 by Team search · 4
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.