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
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
9 comments:
Hi, Brother. i want to spell out in Bangla.
Example: (GK jvL Dwbk nvRvi UvKv gvÎ)
Example's font was sutonny MJ.
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
I want to spell out in bangla.
example: Dwbk nvRvi GKzk UvKv gvÎ
( Examples font was sutonny MJ )
Post a Comment