[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Conversion of NUMBER to WORDS

Posted on 2001-09-10
3
Medium Priority
?
742 Views
Last Modified: 2008-03-10
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
Comment
Question by:manish_saraswat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 6470367
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
 

Accepted Solution

by:
s_lahiri earned 400 total points
ID: 6472507
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
 

Author Comment

by:manish_saraswat
ID: 6473421
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question