## 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;

### 3 Responses to “Function for displaying Rupees in Words”

Ganesh said...

Was looking for similiar information, thanks for sharing.
http://www.alienweb.in/products/classified-portal.html

Anonymous said...

select to_char(to_date(145688,'j'),'Jsp') in_words from dual;

it will give the same output...

Anonymous said...

It is working in Indian style when the amt is in fractions and if not in fractions returning in UK/US fashion.