Transformations: Numbers to Words

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 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 ), can be used.

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 that generate output strings 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( '777777007', 'FF9' ), 'FFSP' ) as words
from dual ;

WORDS
--------------------------------------------------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN

but 777,777,011, which would produce a 79 character string, triggers an error.

select to_char( to_timestamp( '777777011', 'FF9' ), 'FFSP' ) as words
from dual ;
select to_char( to_timestamp( '777777011', '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 the slightly more complex technique presented next.

For Numbers Between -999,999,999 and 999,999,999

To transform nulls, negative numbers, zero, and numbers that are greater than 78 characters long when spelled out 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'
    when abs(i) <= 9999999 or substr( i0, 4 ) = '000000' then
      to_char( to_timestamp( i0, 'FF9' ), 'FFSP' )
    else
      to_char( to_timestamp( substr(i0,1,3), 'FF3' ), 'FFSP' ) ||
      ' ' ||
      to_char( to_timestamp( '000' || substr(i0,4), 'FF9' ), 'FFSP' )
  end as words
from
  ( select i, lpad( abs(i), 9, '0' ) as i0
    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 (see 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, similar to that in the following query.

select replace( 'TWO HUNDRED  MILLION', '  ', ' ' ) fixed from dual ;

FIXED
-------------------
TWO HUNDRED MILLION

JSP Approach

The de facto solution for transforming numbers into words uses the J and JSP format models, which operate on Julian calendar dates. Here is an example of this approach.

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

Unfortunately this approach has 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