Saturday, October 10, 2009

spell out number to in words

A function of spell out number to in words (with JSP)
=======================================================

CREATE OR REPLACE FUNCTION spell_out (p_arg IN NUMBER)
RETURN VARCHAR2 IS
v_result VARCHAR2 (2000);
v_length NUMBER;
v_less_lakh VARCHAR2 (100);
v_crore VARCHAR2 (200);
v_length_crore NUMBER;
BEGIN
v_length := LENGTH (p_arg);

IF p_arg <= 99999 THEN
v_result := TO_CHAR (TO_DATE (p_arg, 'j'), 'Jsp') || 'Taka Only';
ELSIF v_length = 6 THEN
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 2), 'j'), 'Jsp')
|| ' Taka Only';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, 1), 'j'), 'Jsp')
|| ' Lakh';
v_result := v_result || ' ' || v_less_lakh;
ELSIF v_length = 7 THEN
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 3), 'j'), 'Jsp')
|| ' Taka Only';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, 2), 'j'), 'Jsp')
|| ' Lakh';
v_result := v_result || ' ' || v_less_lakh;
ELSIF v_length > 7 THEN
v_length_crore := v_length - 7;
v_crore :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, v_length_crore), 'j'), 'Jsp')
|| ' Crore';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, v_length_crore + 1, 2), 'j'),
'Jsp'
)
|| ' Lakh';
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, v_length_crore + 3), 'j'), 'Jsp')
|| ' Taka Only';
v_result := v_crore || ' ' || v_result || ' ' || v_less_lakh;
END IF;

RETURN v_result;
END;


select to_char(to_date(substr(:p_arg,1,:v_length_crore),'j'),'Jsp')||' Crore' from dual


select to_char(to_date(:p_arg,'j'),'Jsp')||' Taka Only' from dual


select to_char(to_date(:p_arg,'j'),'Jsp') from dual

1 comment:

Tsl said...

I want to spell out in bangla.

example: Dwbk nvRvi GKzk UvKv gvÎ

( Examples font was sutonny MJ )