Search This Blog

Thursday, March 6, 2008

Display Numbers in Figures (words)

It happens very often that there is a requirement to display amounts in words, but in most of the forms we have amounts displayed in numbers. Here I will discuss on various options/methods to meet this.

1) Using Julian method


select to_char(to_date(11221873,'J'),'JSP') from dual -- For upper-case letters

select to_char(to_date(11221873,'J'),'Jsp') from dual -- For mixed-case letters

select to_char(to_date(11221873,'J'),'jsp') from dual -- For lower-case letters

The Julian Method is limited to display text only till million, so 9,999,999 is the maximum value that can be displayed in words.

2) Using Data and Time Suffixes. Listed is the option for Data and Time Suffix
TH - converts numbers to ordinal numbers
SP - converts numbers to words
SPTH - converts numbers to ordinal words

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'Ffsp') AS amt_in_words
FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'ffsp') AS amt_in_words
FROM DUAL -- For Lower Case Letters

The maximum value that can be displayed is 99,999,999. So we added another number as against the Julian function.

3) This is the best option but limited only to Oracle Application users.
Oracle Apps has provided an inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
FROM DUAL -- For Lower Case Letters

Well as there is an end to everything, even this function has a limitation but it can display big enough as it goes upto hundred billions. 999,999,999,999 is the maximum allowed value.

5 Comments:

Anonymous said...

please i do this and it works is there any way to do this but the result returns in arabic words ???

Unknown said...

kindly guide me how i apply TO_TIMESTAMP function in oracle 6i,
because i am in big trouble... i want to convert (1721370) number to words.when i apply julian function its return error:

Suresh Vaishya said...

Mahesh .. this should work as it is in 6i as well. Where are you trying this reports or forms?

Anonymous said...

Shall we the Indian style format

Mohamed sabry said...

There is a limitation on method number (2)
try this:

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(85893437, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
FROM DUAL

it will throw an error :
ORA-01877: string is too long for internal buffer

The reason is : the resulted text is longer than 78 characters , and it will happen with a lot of numbers smaller than 99999999

i don't know how to solve it , some one advised to cast it as varchar2(100)
(here : http://laurentschneider.com/wordpress/2008/08/return-size-of-to_char.html)
, but this is not working for me on an oracle database

Copyright (c) All rights reserved. Presented by Suresh Vaishya