Re: does anyone have a shareware pl/sql algorythm?
Date: 22 Oct 2001 08:24:22 -0700
Message-ID: <c98e8555.0110220724.aad8810_at_posting.google.com>
> > >for converting numeric US dollars to a string for check writing that I can
> > >
> > >eg. $200.00 ---> Two Hundred and 00/100 Dollars
I found a different version a while back...this will only handle numbers between -5,373,484 and 5,373,484. But, it is clean and easy. -C
Convert Numbers to Words
by Brian Membrey, The PaperBag Software Company Pty Ltd
I guess a mundane exercise that most programmers cop at some stage is
having to convert a number (123) into a text equivalent (ONE HUNDRED
AND TWENTY THREE) - for cheques, group certificates, etc. Probably the
code involved a loop stripping out the numerals and then applying a
value according to the relative position within the overall value.
Although it winds a strange path via date functions, SQL*Plus actually
provides a mechanism for automating much of this process. Executing
the following :
SELECT TO_CHAR ( TO_DATE ( TO_CHAR ( 103465, '99999999999') ,
'J'), 'JSP') FROM dual;
returns a value of ONE HUNDRED THREE THOUSAND FOUR HUNDRED SIXTY-FIVE
If we break the statement into each component function, then what
happens is :
the inner TO_CHAR simply converts the number (which would probably be
a numeric variable in practice) to CHAR so some magic can happen ...
the TO_DATE converts the CHAR using the J (Julian day) format. (the
Julian day is the number of days since January 1, 4712BC, which is
when SQL*Plus was invented),
having established the date value, we then convert that date back to a
Julian day. Because the TO_CHAR in this case is used in DATE context,
we can use the J mask to duplicate the original value, and append the
SP (spell) format mask. 'Spell" does exactly that - it converts the
number to words, hence the string value above.
SP can be used in a number of situations. For example, if SYSDATE is
26-AUG-98, then :
SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual; -- spells the day
as Twenty-Six,
and
SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual; --returns
TWENTY-SIXTH
Some simple manipulations can be included with the base conversion to
cover floating numbers or currencies (email brianm_at_lt.com.au for
source), eg. 103465.27 becomes ONE HUNDRED AND THREE THOUSAND FOUR
HUNDRED AND SIXTY-FIVE DOLLARS AND TWENTY-SEVEN CENTS.
One covenant however : if in your mad appreciation of this trivia you
want to send me a cheque for more than $5,373,484.00, then you'll have
to write it manually, or send more than one cheque!
SQL*Plus restricts Julian days to between 1 and 5373484, which won't
be a problem for most applications, but should be borne in mind before
using the technique in anger.
5373484 represents 31-Dec-9999, so this may be Oracle's way of
introducing us to a Year 10K problem!
Received on Mon Oct 22 2001 - 17:24:22 CEST