• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • Last Modified:

Conversion of NUMBER to WORDS

Hi there !!

I want to write a function or procedure which accepts a decimal number as its arguement and returns the same number translated into words.

The procedure should give me the following output for given inputs :

1) INPUT : 2300.34
   OUTPUT : Rupees Two Thousand Three Hundred and Paise Thirty Four Only.

2) INPUT : 1523420.25
   OUTPUT : Rupees Fifteen Lacs Twenty Three Thousand Four Hundred Twenty and Paise Twenty Five Only.

3) INPUT : 23455255.00
   OUTPUT : Rupees Two Crore Thirty Four Lacs Fifty Five Thousand Two Hundred Fifty Five and Paise Zero Only.

Here the maximum number I will input will be 99999999.99 and there will be only 2 decimal places. The number before the decimal point is RUPEES and the number after the decimal point is PAISE.

I have tried a lot but I am unable to get it. Please send me the PL/SQL for this procedure.

Regards,
Manish.
0
manish_saraswat
Asked:
manish_saraswat
1 Solution
 
schwertnerCommented:
Look at the following example and try to construct the solution:


1* select to_char( to_date(5373484,'J'),'Jsp') from dual
SQL> /

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four

This will work for numbers between 1 and 5,373,484...
You can take it a step further to support numbers -5,373,484 .. 5,373,484 by
select decode( sign( :N ), -1, 'Negative ', 0, 'Zero', NULL ) | |
decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') )
from dual
/
That covers the integer numbers.


SELECT TO_CHAR(TO_DATE(1234567,'J'),'JSP')
FROM dual
That's the maximum

Try using the spelcheck.sql file found in chapter 5 of the book 'Oracle8 PL/SQL programming' by Scott Urman. The maximum no you can spell out is 10**10. I have sent the spelcheck.sql file by mail to you. Try using that.
0
 
s_lahiriCommented:
Try this and this should give you the result you are looking for:

select 'Rupees ' ||
decode(floor(&&amt/power(10,7)),0,'',
to_char(to_date(floor(&&amt/power(10,7)),
'J'),'JSP') || ' crore ') ||
decode(floor((&&amt-power(10,7)*floor(&&amt/power(10,7)))/power(10,5)),0,'',
to_char(to_date(floor((&&amt-power(10,7)*floor(&&amt/power(10,7)))/power(10,5)),
'J'),'JSP') || ' lac ') ||
decode(floor((&&amt-power(10,5)*floor(&&amt/power(10,5)))/power(10,3)),0,'',
to_char(to_date(floor((&&amt-power(10,5)*floor(&&amt/power(10,5)))/power(10,3)),
'J'),'JSP') || ' thousand ') ||
decode(floor((&&amt-power(10,3)*floor(&&amt/power(10,3)))/power(10,2)),0,'',
to_char(to_date(floor((&&amt-power(10,3)*floor(&&amt/power(10,3)))/power(10,2)),
'J'),'JSP') || ' hundred ') ||
decode(floor((&&amt-power(10,2)*floor(&&amt/power(10,2)))/power(10,0)),0,'',
to_char(to_date(floor((&&amt-power(10,2)*floor(&&amt/power(10,2)))/power(10,0)),
'J'),'JSP') || ' ') ||
'and Paise ' ||
decode(power(10,2)*(&&amt-floor(&&amt)),0,'Zero',
to_char(to_date(power(10,2)*(&&amt-floor(&&amt)),
'J'),'JSP') ) ||
' Only'
from dual
/

Cheers ..
0
 
manish_saraswatAuthor Commented:
Hi there !!!

schwertner's answer was too good and correct too but as I am in India, here the numbering system of MILLIONS is not valid. So I accept the answer of s_lahiri. Hey lahiri yor answer was too good and worked PERFECTLY !!! Thanx a lot and this made my report to work.

Have a GREAT time,
Manish.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now