Oracle PLSQL Number to Word

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



Muhammad Abdullah Al Noor

Muhammad Abdullah Al Noor, An Oracle Apex Consultants and founder of Noors Technology (www.noorstech.com). Core Expertise : Database Administration, Oracle Forms and Reports Development, Oracle Apex Application Designer and Development, Linux Professional etc. Also the owner of TrainerBD Training and OraDemy E-Learning. WhatsApp +8801790721177

Post a Comment

Previous Post Next Post