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.