1 2 3 Previous Next 32 Replies Latest reply: May 14, 2007 2:52 AM by 568542

# Convert amount in words to amount in numbers

I am trying to convert an amount in words to numbers. Can anyone help?
• ###### 2. Re: Convert amount in words to amount in numbers
You're going from words to numbers, right?

(usually the request is the other way around)
• ###### 3. Re: Convert amount in words to amount in numbers
this?
```SQL>  select to_char(to_date(1311,'J'), 'JSP') as converted_form from dual;

CONVERTED_FORM
---------------------------------------------------------------------------
ONE THOUSAND THREE HUNDRED ELEVEN```
• ###### 4. Re: Convert amount in words to amount in numbers
SomeoneElse is a much better reader than I am. I was assuming that you were going from numbers to words.

Justin
• ###### 5. Re: Convert amount in words to amount in numbers
Sorry !!!
Same here Justin.
• ###### 6. Re: Convert amount in words to amount in numbers
I'll bet you guys were right though. The OP probably just made a typo.

At least I hope so. Going from words to numbers would be decidedly more difficult.
• ###### 7. Re: Convert amount in words to amount in numbers
To take the example of Tom Kyte, linked by Justin, that seems a huge work to build such function :
```Twelve undecillion Three Hundred Forty-Five decill
ion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillio
n Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quad
rillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundr
ed Forty-Five thousand Six Hundred Seventy-Eight```
...

Nicolas.
• ###### 8. Re: Convert amount in words to amount in numbers
It was no typo. I would like to go from words to decimal. But I can see from your replies that it is a very difficult task and will seek a workaround.
• ###### 9. Re: Convert amount in words to amount in numbers
It was no typo.
Wow.

That would be difficult because writing numbers as words is not consistent. For example,

4400 can be written as "Four Thousand Four Hundred" or "Forty Four Hundred".

87 can be written as "Four Score And Seven".
• ###### 10. Re: Convert amount in words to amount in numbers
As long as you adhere to the standard Oracle word formatting techniques and the numbers are not too big...
```SELECT  LEVEL+1 asnumber
FROM dual
WHERE to_char(to_date(LEVEL+1,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN';

ASNUMBER
----------
1111```
Greg Pike
http://www.singlequery.com
• ###### 11. Re: Convert amount in words to amount in numbers

Nicolas.
• ###### 12. Re: Convert amount in words to amount in numbers
LOL!! Nicolas is of course correct. ONE not only doesn't work but leaves you with a pretty serious problem as well. If you choose this method, I would also recommend adding a clause that allows the query to complete if there is a typo or something. Just use one higher than the maximum number you expect to receive.
```SELECT  LEVEL
FROM dual
WHERE to_char(to_date(LEVEL,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN'
AND LEVEL < 10001;  -- Your get out of jail clause!```
Greg Pike
http://www.singlequery.com
• ###### 13. Re: Convert amount in words to amount in numbers
Guys, I am a bit hesitant to say but I am not following you guys at all....I thought I was good in plsql but have never seen a query like gfpike's one before....What is the 'J' and the 'JSP' and the 'CONNECT By' and 'LEVEL' stuff...

I am using oracle 8...is it even supported....

I pasted in SQL Worksheet and got the following:
SQLWKS> SELECT LEVEL
2> FROM dual
3> WHERE to_char(to_date(LEVEL,'J'), 'JSP') = 'ONE THOUSAND ONE HUNDRED ELEVEN'
4> CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP') != 'ONE THOUSAND ONE HUNDRED ELEVEN'
5> AND LEVEL < 10001;
LEVEL
----------
ORA-01436: CONNECT BY loop in user data
SQLWKS> SELECT LEVEL
2> FROM dual
3> WHERE to_char(to_date(LEVEL,'J'), 'JSP') = 'ONE THOUSAND ONE HUNDRED ELEVEN'
4>
ORA-01854: julian date must be between 1 and 5373484
• ###### 14. Re: Convert amount in words to amount in numbers
CONNECT BY ... LEVEL wasn't available in Oracle 8. There is probably no easy way to back-port the CONNECT BY approach to Oracle 8.

Depending on how many numbers we're talking about, however, you could create a WORDS_TO_NUMBER mapping table, use the number to words mapping function to generate the text for all the numbers you'd want to convert, and then write a function to do the lookup. Probably not practical if you've got billions of strings that need to be converted, but it might be reasonable for some situations.

Justin
1 2 3 Previous Next