Number to Words

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 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