Re: Resend - Numbers to Alpha

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/02/25
Message-ID: <34f57cd0.5642072_at_192.86.155.100>#1/1

A copy of this was sent to Joe Nametko <Joseph.Nametko_at_alliedsignal.com> (if that email address didn't require changing) On Tue, 24 Feb 1998 08:12:02 -0500, you wrote:

>Does anyone have a pl/sql or sql routine to convert (for display and
>printing) fields that contain a number?
>For example, if the field contains: 1105.75 I need to display/print:
>ONE THOUSAND ONE HUNDRED FIVE DOLLARS and 75 CENTS.
>I'm sure I've seen it and I know I can do it, but why re-invent the
>wheel?
>Thanks,
>Joe Nametko

You can use a date function to do this....

variable N number
column spelled_out format a75
exec :n := 1105.75;

select decode( sign( trunc(:N) ), -1, 'Negative ', 0, 'Zero', NULL ) ||

       decode( sign( abs(trunc(:N)) ),
                    +1, to_char( to_date( abs(trunc(:N)),'J'),'Jsp') ) ||
       decode( sign( :N-trunc(:N) ),
                    +1, ' and ' || trunc((:N-trunc(:N))*100) || ' Cents' )
                    Spelled_Out
       from dual

/

SPELLED_OUT

---

One Thousand One Hundred Five and 75 Cents

This works for numbers in the range -5,373,484 to +5,373,484  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 25 1998 - 00:00:00 CET