The following function will convert any number to Word Text Format:
CREATE OR REPLACE Function LIERP.Wrd(N real default 0,M number default 0) return Varchar2 IS
x number(14,2); --:=round(least(n,999999999999.99),2);
b char(12); --:=lpad(floor(x),12,'0');
c char(12); --:='0000000000'||rpad((x-floor(x))*100,2,'0');
w varchar2(400);
function F_Spell(a char) return varchar2 is
l number:=1;
v varchar2(200);
type vnum is varray(6) of real;
vt vnum:=vnum(substr(a,1,2),substr(a,3,3),substr(a,6,2),substr(a,8,2),substr(a,10,3));
---------------1-Thousand-------2-Crore---------3-Lac-----4-Thousand----- 5---
begin
l:=1;
while l<=5 loop
if to_number(vt(l))>0 then
v:=ltrim(rtrim(nvl(v,' ')))||' '||rtrim(translate(to_char(to_date(to_number(vt(l)),'YYYY'),'YyyySP'),'-',' '));
if l=1 or l=4 then v:=ltrim(rtrim(v))||' Thousand';
elsif l=2 then v:=ltrim(rtrim(v))||' Crore';
elsif l=3 then v:=ltrim(rtrim(v))||' Lac'; end if;
end if;
l:=l+1;
end loop;
return v;
end F_Spell;
BEGIN
if abs(N)>999999999999.99 then w:='Error: Numeric Value Over Limit'; x:=0;
elsif abs(N)=0 then w:='Zero'; else x:=round(abs(N),2);
b:=lpad(floor(x),12,'0');
c:='0000000000'||rpad((x-floor(x))*100,2,'0');
if to_number(nvl(b,0))>0 then w:=F_Spell(b); if m=1 then w:='Taka '||ltrim(rtrim(w)); end if; end if;
if to_number(nvl(c,0))>0 then
if length(ltrim(rtrim(w)))>0 then
if m=0 then w:=ltrim(rtrim(w))||' and '||ltrim(rtrim(F_Spell(c)));
else w:=ltrim(rtrim(w))||' and '||ltrim(rtrim(F_Spell(c)))||' Paisa'; end if;
else w:=ltrim(rtrim(F_Spell(c))); if m=1 then w:='Paisa '||ltrim(rtrim(w)); end if; end if;
end if; if m=1 and w is not null then w:=ltrim(rtrim(w))||' Only'; end if;
if N<0 then w:='Negative '||ltrim(rtrim(w)); end if; end if;
RETURN ltrim(rtrim(w));
END Wrd;
/
