Re: numbers as words

From: Carl Gohringer <cgohring_at_lucifer>
Date: Fri, 30 Sep 1994 10:43:19 GMT
Message-ID: <Cwxv48.7uM_at_nl.oracle.com>


john_at_wpa.com (John Bartley) writes:
: I'm looking for some SQL coding techniques or at least a reasonable
: conceptual approach to printing check amounts as words, e.g., the check
: amount is printed in numbers as $9,999.99 and printed on the next line as
: Nine Thousand Nine Hundred Ninety-nine and 99/100.

The script below is based on a table called 'chk' with a single column 'num'. This column is defined as NUMBER(7,2).

     SQL> set head on
     SQL> select * from chk;
 
         NUM                                                                   
     -------                                                                   
         .34                                                                   
     1234.45                                                                   
        3445                                                                   
 
     SQL> set head off
     SQL> _at_chk
 
     ZERO DOLLARS AND THIRTY-FOUR CENTS                                        
     ONE THOUSAND TWO HUNDRED THIRTY-FOUR DOLLARS AND FORTY-FIVE CENTS         
     THREE THOUSAND FOUR HUNDRED FORTY-FIVE DOLLARS AND ZERO CENTS             
 
     SQL> l
       1  select decode(instr(num,'.'),1,'ZERO',to_char(to_date(
       2           substr(num,1,decode(instr(num,'.'),0
       3  ,length(num),instr(num,'.')-1))
       4  ,'J'),'JSP'))||' DOLLARS AND '||
       5  decode(length(num)-instr(num,'.'),1,
       6  to_char(to_date(
       7      rpad(substr(num,instr(num,'.')+1,length(num)),2,0)
       8  ,'J'),'JSP')
       9  ,decode(instr(num,'.'),0,'ZERO',
      10  to_char(to_date(
      11                  substr(num,instr(num,'.')+1,length(num))
      12  ,'J'),'JSP')))||' CENTS '
      13* from chk
  
     SQL> spool off

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, European Development Centre 
Oracle Park, Bittams Lane, Chertsey, Surrey, England, KT16 9RG
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+
Received on Fri Sep 30 1994 - 11:43:19 CET

Original text of this message