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 |