Amount in words

[badrinathn]

Hi,
Can any one tell me, which is the function/package used to convert the amount (or any number) into words. This kind of conversion is required to print amount in words on the check.
Badrinath

[omegamark]

There is a Function called jsp

[omegamark]

SQL> select to_char(to_date(123456,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(123456,'J'),'JSP')
--------------------------------------------------------
one hundred twenty-three thousand four hundred fifty-six

[tamilselvan]

Another Function to return word for a number:
create or replace function NUM_TO_WORD (P_inp_num in NUmber)
return Varchar2 as
--
-- Functions called : Digits3_To_Word
--
-- AUthor : Tamilselvan
-- Number to word function
-- Description:
-- Number to word conversion
--
-- History of Modification
-- Sep 22, 2001 Written
-- Sep 24, 2001 Tested for Cents
--
-- -------------------------------------------------------------------

L_Return Varchar2(1000) ;

m Number := 0 ;

V_inp_Num Number := 0 ;
V_inp_Int Number := 0 ;
V_inp_dec Number := 0 ;
V_Char Varchar2(100) ;
V_Temp Number := 0 ;

Type LionArray is table of Varchar2(500) ;
In_str LionArray :=
LionArray ( '',
'Thousand ' , -- 10 to the power 3
'Million ' , -- 10 to the power 6
'Billion ' , -- 10 to the power 9
'Trillion ' , -- 10 to the power 12
'Quadrillion ' , -- 10 to the power 15
'Quintillion ' , -- 10 to the power 18
'Sextillion ' , -- 10 to the power 21
'Septillion ' , -- 10 to the power 24
'Octillion ' , -- 10 to the power 27
'Nonillion ' , -- 10 to the power 30
'Decillion ' , -- 10 to the power 33
'Undecillion ' , -- 10 to the power 36
'Duodecillion '); -- 10 to the power 39
Begin
if p_inp_num < 0 then
V_inp_Num := (P_inp_Num * -1) ;
else
V_inp_Num := P_inp_Num;
end if;
V_inp_Int := Trunc(V_inp_Num) ;
V_inp_Dec := V_inp_Num - V_inp_Int ;
if V_inp_Dec > 0 then
V_inp_dec := V_inp_dec * 100 ;
V_inp_Dec := to_number(substr(to_char(V_inp_Dec),1,2)) ;
end if ;

V_char := to_char(V_inp_Int) ;

for m in 1..In_Str.Count Loop
exit when V_char is NULL ;
V_Temp := To_Number(Substr(V_char,(Length(V_char)-2), 3));

if V_temp > 0 then
L_Return := Digits3_To_Word(V_Temp) || In_str(m) || L_Return ;
end if ;

V_char := Substr(V_char,1,(Length(V_Char)-3));

End Loop ;

if V_inp_Int > 0 then
L_Return := 'Dollar '||L_Return ||' and ';
elsif V_inp_Int = 0 then
L_return := 'Dollar Zero and ' ;
end if ;

-- Concatenate the word for decimal digits
if V_inp_Dec > 0 then
L_return := L_return || Digits3_To_Word(V_inp_Dec) ||' Cents';
elsif V_inp_Dec = 0 then
L_Return := L_return ||' Zero Cents';
End if ;
-- -------------------------------

Return L_return;
Exception when others then
return NULL ;
END;
/

create or replace function Digits3_To_Word (P_number in number)
return varchar2 as
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
--
L_Return2 varchar2(500) := NULL ;

i Number := 0 ;
j Number := 0 ;
k Number := 0 ;

V_inp_Number Number := 0 ;
V_temp Varchar2(50) := NULL;

type OneArray is table of varchar2(50);
type TenArray is table of varchar2(50);
OneStr OneArray := OneArray('One ',
'Two ',
'Three ',
'Four ',
'Five ',
'Six ',
'Seven ',
'Eight ',
'Nine ',
'Ten ',
'Eleven ',
'Twelve ',
'Thirteen ',
'Fourteen ',
'Fifteen ',
'Sixteen ',
'Seventeen ',
'Eighteen ',
'Nineteen ');
TenStr TenArray := TenArray('',
'Twenty ',
'Thirty ',
'Forty ' ,
'Fifty ',
'Sixty ',
'Seventy ',
'Eighty ',
'Ninety ');

BEGIN

V_inp_Number := P_Number ;
if V_inp_number > 999 then
V_inp_number := 0 ;
end if ;

V_temp := LPAD(to_char(V_inp_number),3,0) ;

-- Find Hundredth position
i := to_number(substr(V_temp,1,1));
if i > 0 then
L_Return2 := OneStr(i)||'Hundred ' ;
end if ;

-- Find last 2 digits
i := to_number(substr(V_temp,2,2));
j := to_number(substr(V_temp,2,1));
k := to_number(substr(V_temp,3,1));
if i > 0 and i < 20 then
L_Return2 := L_Return2 || OneStr(i) ;
end if ;
if j >= 2 then
L_Return2 := L_Return2 || TenStr(j) ;
if k > 0 then
L_Return2 := L_Return2|| OneStr(k) ;
end if;
end if;

Return L_Return2 ;
EXCEPTION WHEN OTHERS
THEN NULL ;
END ;
/

[sambavan]

As this thread would be more appropriate under development forum rather than in the administartion forum, I'm moving it to the development forum from the administration forum

Sam

[Raminder]

I read this thread with interest.
JSP function had its limitations

SQL> select to_char(to_date(8500648,'J'),'JSP') from dual
2 /
select to_char(to_date(8500648,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484

Another thing. Can it be used without the 'to_date' function?

tamilselvan has given a nice set of functions.

So I also tried out my hand to produce one:(!!)
(of course it does'nt go as far as to include negatives and cents. But I think can be modified to include that also)
I used heavily 'decode' function form dual (god bless the man who gave this table in Oracle).
I also relied on place values to get the figure.

Comments Invited!

---------------------NUMBER TO TEXT CONERSION FUNCTION---
----------------------------
-- Developed by Raminder Singh
-- Sep 28, 2001

--The following function will accept any number between 0 ------and 9999999999
--and convert it into a text string.
--This function has been created primarily for Indian text
--representation of numbers which are in 'Crores' and 'Lakhs'.

Create or replace function NUM_CONV(N IN NUMBER)
return varchar2
as
D number;
Y number;
mytwo boolean:=False;

--compute the length of given number

L number:=length(N);

Placevalue number:=0;
mystring varchar(400);
string1 varchar(40);
string2 varchar(40);

Begin
--Error coding if number is outside limits
if N < 0 or N > 9999999999 then
mystring:='Out of range: This function cannot convert the given number to text';
elsif N = 0 then
mystring:='Nil';
else

--Loop from 1st to last digit of number

for X in 1..L loop

--Find the digit in each place

D:=Substr(N,X,1);

--Find the place value of a given digit. For example, place -------value of 5 in 5436 is 5000

Placevalue:=D;
Y:=L-X;
if Y > 0 then
for Z in 1..Y loop
Placevalue:=Placevalue*10;
end loop;
end if;

--Once 'Place Value' of the digit is known, carry on with ---------conversion

--This 'if' block is to avoid reading the second digit of a 2 --------digit block like 12(twelve)

if mytwo=True then
mytwo:=false;
string1:=null;
else

--Following block is to find out if the digit need be read out ------singly or in relation with next digit (like 11 to 19)

if Length(Placevalue)=2 or Length(Placevalue)=5 or Length(Placevalue)=7 or Length(Placevalue)=9 or Length (Placevalue)=11 then
if substr(placevalue,1,1)=1 and substr(N, X+1,1) between 1 and 9 then
select decode(substr(N, X,2),11, 'Eleven', 12, 'Twelve', 13,'Thirteen', 14,'Fourteen', 15,'Fifteen', 16,'Sixteen', 17,'Seventeen', 18,'Eighteen', 19,'Nineteen', 0, Null) into string1 from dual;
mytwo:=True;
else
select decode(substr(placevalue,1,1),1, 'Ten', 2, 'Twenty', 3,'Thirty', 4,'Forty', 5,'Fifty', 6,'Sixty', 7,'Seventy', 8,'Eighty', 9,'Ninety', 0, Null) into string1 from dual;
end if;
else
select decode(substr(placevalue,1,1), 1, 'One', 2, 'Two', 3,'Three', 4,'Four', 5,'Five', 6,'Six', 7,'Seven', 8,'Eight', 9,'Nine', 0, Null) into string1 from dual;
end if;
end if;

--Now find the second string describing the number

if D<>0 then
select decode(placevalue/D, 100, 'Hundred', 1000,'Thousand', 100000,'Lakh', 10000000,'Crore', 1000000000, 'Hundred') into string2 from dual;
elsif D=0 and substr(N, X-1,1)<>0 and Y< 9 then
select decode(Y, 7,'Crore', 5, 'Lakh', 3, 'Thousand') into string2 from dual;
end if;

--This code block is primarily to reduce unnecessary gaps -------between strings

if string1 is null then
string1:=string2;
elsif string2 is null then
string1:=string1;
else
string1:=string1||' '||string2;
end if;

if string1 is null then
mystring:=mystring;
else
mystring:=mystring||' '||string1;
end if;

--reinitialize second string

string2:=Null;

end loop;
end if;
return mystring;
end;
/


--COMMENTS SOLICITED--

[tamilselvan]

In India, business community now start writing Million and Billion. The problem in writing words for Number in Rs and Paise is it ends with crores.

So, it is easy and nice to use Million, Billion, Trillion etc.

[Raminder]

Reserve Bank of India, several other govt banks, departments and even multinational banks use Lakhs and Crores.
(Last week I got a preapproved loan offer of Rs 1 lakh from citibank.)
So I think this terminology is here to stay for quite a few years. Someone will have to code for this....

[Jani]

What r the meanings of 'j' and 'jsp' in the statement and why not we use more digits in the statement and fractional nos.

Thanx

[OCPIP]

'j' means number of julian days since Jan 01, 4713 BC and 'sp' is to spell out the number.
for ex
select to_char(sysdate,'DDsp') from dual;