Slavko's Notes
Printing Numbers As Words


Have you ever wanted to print the amount of a check in words? About six or seven years ago, I wrote a PASCAL program for a friend to do this. It's a real pain. And then came ORACLE. What took a couple of hundred lines of code can now be done in one simple command using SQL:


SELECT DECODE(TRUNC(&&N/1000),
                 0, NULL
                  , TO_CHAR(TO_DATE('01-JAN-' || TRUNC(&&N/1000),
                                     'DD-MON-YYYY'
                                   ), 'YYYYSP'
                           )
                    || ' THOUSAND ')
       || DECODE(MOD(&&N, 1000),
                   0, NULL
                    , TO_CHAR(TO_DATE('01-JAN-' || MOD(&&N, 1000), 'DD-MON-YYYY')
                              , 'YYYYSP'
                             )
                ) 
  FROM SYSTEM.DUAL;

In PL*SQL for SQL*Forms the following would do the trick:


begin
    if trunc(:GLOBAL.N/1000) == 0
    then
        :GLOBAL.WORDS := NULL;
    else
        :GLOBAL.WORDS
            := to_char(to_date('01-JAN-' || trunc(:GLOBAL.N/1000), 'DD-MON-YYYY')
                       , 'YYYYSP'
                      )
               || ' THOUSAND ';
    end if;
    if mod(:GLOBAL.N, 1000) != 0
    then
        :GLOBAL.WORDS 
            := :GLOBAL.WORDS || to_char(to_date('01-JAN-' || mod(:GLOBAL.N, 1000)
                                                , 'DD-MON-YYYY'
                                               ), 'YYYYSP'
                                       );
    end if;

This example will print any number from 1 to NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED AND NINETY-NINE. It is possible to go to 999,999,999 or even to 999,999,999,999 if you really want to.


© Copyright 1995 by Starsoft Inc. This may be used for non-commercial use as long as credit is given to the author.

[Feedback]