Re: Numeric Money Conversion to Words

[Surit Maharana <MaharanaS@EPENERGY.COM>]

(Oracle specific. Can be customized for others too)

Hi Mark,

Using Julian date format (J) and spell-out(SP) suffix of Oracle, any
numeric money can be converted to words easily. You can play with
'j' and 'sp' to get required case also. Try the following in SQL*Plus.

select  decode(instr(amount,'.'),1,'ZERO',to_char(to_date(substr
        (amount,1,decode(instr(amount,'.'),0,length(amount),
        instr(amount,'.')-1)),'J'),'JSP'))||' AND '||decode(instr
        (amount,'.'),0,'00',substr(amount,instr(amount,'.')+1))||'/100'
from dual
/

150             one hundred fifty and 00/100

123001.25       One Hundred Twenty-Three Thousand One And 25/100

..75            ZERO AND 75/100

Good Luck


Surit Maharana                           surit@workmail.com
PeopleSoft/Oracle DBA



PS:- As it can be seen, the amount should be char-type.

>----------
>From:  Mark W. Salem[SMTP:MSALEM2306@AOL.COM]
>Sent:  Sunday, February 02, 1997 6:11 AM
>To:    Multiple recipients of list SQR-USERS
>Subject:       Numeric Money Conversion to Words
>
>Help!  I am in desperate need of a solution to converting a numeric
>money
>field into its word equivilent; e.g.,   $1,652.97 -->   One thousand -
>six
>hundred - fifty - two and 97/100
>
>I am using Oracle on a Unix server with a Window's 95 client...
>
>Any suggestions?
>