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