This tutorial demonstrates how to transform numbers like 1, 12, and 123 into English words like "ONE", "TWELVE", and "ONE HUNDRED TWENTY-THREE". This transformation is a common requirement for cheque printing applications.
For Numbers Between 0 and 9,999,999
For numbers between 0 and 9,999,999 use the following technique which leverages the FF datetime formatting element (introduced in Oracle 9i) and the SP datetime format suffix (see Dates and Times: Format Suffixes ).
set linesize 100 set recsep off column numbers format a12 column words format a80 word_wrapped set null '(null)' select to_char(i,'999,999,999') numbers , to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) as words from n2w where i between 0 and 9999999 order by i ;
NUMBERS WORDS
------------ --------------------------------------------------------------------------------
0 ZERO
1 ONE
12 TWELVE
123 ONE HUNDRED TWENTY-THREE
1,234 ONE THOUSAND TWO HUNDRED THIRTY-FOUR
12,345 TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
123,456 ONE HUNDRED TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX
1,234,567 ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN
1,721,057 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-SEVEN
1,721,058 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-EIGHT
1,721,300 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND THREE HUNDRED
1,721,423 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FOUR HUNDRED TWENTY-THREE
1,721,424 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FOUR HUNDRED TWENTY-FOUR
5,373,484 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
5,373,485 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FIVE
7,777,777 SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN
9,999,999 NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
This technique also works for many values larger than 9,999,999, but those whose output strings are greater than 78 characters long will trigger an error (as tested in Oracle 10g XE).
For example, the number 777,777,007 produces a 78 character string without a problem
select to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) as words from n2w where i = 777777007 ;
WORDS -------------------------------------------------------------------------------- SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN
but 777,777,011, which should produce a 79 character string, triggers an error.
select to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) as words
from n2w
where i = 777777011 ;
select to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) as words
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer
To work around this problem we can use CAST (see Laurent Schneider's blog post on return size of to_char for more info.).
select CAST( to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) AS VARCHAR2(100) ) as words from n2w where i = 777777011 ;
WORDS -------------------------------------------------------------------------------- SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND ELEVEN
For Numbers Between -999,999,999 and 999,999,999
To transform nulls and negative numbers as well as positive number we can use the following technique.
select
to_char(i,'999,999,999') numbers ,
case sign( i )
when -1 then 'NEGATIVE '
else null
end ||
case
when i is null then
'NOT ASSIGNED'
else
cast
(
to_char
( to_timestamp( lpad(abs(i),9,'0'), 'FF9' ), 'FFSP' )
as varchar2(100)
)
end as words
from
n2w
order by i
;
NUMBERS WORDS
------------ --------------------------------------------------------------------------------
-123,456,789 NEGATIVE ONE HUNDRED TWENTY-THREE MILLION FOUR HUNDRED FIFTY-SIX THOUSAND SEVEN
HUNDRED EIGHTY-NINE
-1 NEGATIVE ONE
0 ZERO
1 ONE
12 TWELVE
123 ONE HUNDRED TWENTY-THREE
1,234 ONE THOUSAND TWO HUNDRED THIRTY-FOUR
12,345 TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
123,456 ONE HUNDRED TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX
1,234,567 ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN
1,721,057 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-SEVEN
1,721,058 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-EIGHT
1,721,300 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND THREE HUNDRED
1,721,423 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FOUR HUNDRED TWENTY-THREE
1,721,424 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FOUR HUNDRED TWENTY-FOUR
5,373,484 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
5,373,485 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FIVE
7,777,777 SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN
9,999,999 NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE
12,345,678 TWELVE MILLION THREE HUNDRED FORTY-FIVE THOUSAND SIX HUNDRED SEVENTY-EIGHT
20,000,000 TWENTY MILLION
77,777,777 SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED
SEVENTY-SEVEN
123,456,789 ONE HUNDRED TWENTY-THREE MILLION FOUR HUNDRED FIFTY-SIX THOUSAND SEVEN HUNDRED
EIGHTY-NINE
200,000,000 TWO HUNDRED MILLION
777,777,007 SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN
777,777,011 SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND ELEVEN
777,777,777 SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN
HUNDRED SEVENTY-SEVEN
(null) NOT ASSIGNED
Gotchas
Extra Spaces
FFSP generates extra spaces between some words, like the value
TWO HUNDRED MILLION
in the results of the preceeding query. If this is a problem the result can be corrected with a little extra logic. Just use REPLACE on the transformed value.
select
replace
( cast
( to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' )
as varchar2(100)
)
, ' '
, ' '
) as words
from n2w
where i = 200000000 ;
WORDS -------------------------------------------------------------------------------- TWO HUNDRED MILLION
JSP Approach
Before I published my FFSP technique here in June 2007 the de facto solution for transforming numbers into words was the JSP approach, which operates on Julian calendar dates. Beware of the JSP approach. It has some serious limitations that are not immediately obvious when you first use it. This is how it typically works
select to_char(i,'999,999,999') numbers , to_char( to_date( i, 'J' ), 'JSP' ) as words from n2w where test_group = 'A' ;
NUMBERS WORDS
------------ --------------------------------------------------------------------------------
(null) (null)
1 ONE
12 TWELVE
123 ONE HUNDRED TWENTY-THREE
1,234 ONE THOUSAND TWO HUNDRED THIRTY-FOUR
12,345 TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
123,456 ONE HUNDRED TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX
1,234,567 ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN
1,721,057 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-SEVEN
1,721,424 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FOUR HUNDRED TWENTY-FOUR
5,373,484 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
and these are the limitations. First, the technique does not work with the number zero.
select to_char( to_date( 0, 'J' ), 'JSP' ) as words
from dual ;
select to_char( to_date( 0, 'J' ), 'JSP' ) as words
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
Second, it does not support numbers greater than 5,373,484, which is the date 9999-12-31 in Julian days.
select to_char( to_date( 5373484 + 1, 'J' ), 'JSP' ) as words
from dual ;
select to_char( to_date( 5373484 + 1, 'J' ), 'JSP' ) as words
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
Third, since the Julian calendar does not have a year zero in it the technique fails for numbers between 1,721,058 (which would be 0000-01-01) and 1,721,423 (which would be 0000-12-31).
select to_char( to_date( 1721058, 'J' ), 'JSP' ) as words
from dual ;
select to_char( to_date( 1721058, 'J' ), 'JSP' ) as words
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
select to_char( to_date( 1721423, 'J' ), 'JSP' ) as words
from dual ;
select to_char( to_date( 1721423, 'J' ), 'JSP' ) as words
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
See Also
The FFSP solutions presented above will not work on Oracle databases prior to 9i or with numbers greater than 999,999,999. For these situations a number of alternative solutions are available at Ask Tom "Spell the number".
Tags
| Numeric Digits to English Words |
| Print Dollar Amount in Words on Checks |
| Spell a Number |
| Spelling Numbers as Words |
| Translate Numbers into Words |