You should be able to work out what you want from this example. I think I got the basic logic from an old document on MetaBlink titled "Bul# 99802.782 DOLLARS INTO WORDS FOR CHECKS". (Gotta luv that old decode command)
create table chk(num number); insert into chk values(1); insert into chk values(12345); insert into chk values(0); insert into chk values(0.1); insert into chk values(12345.12); insert into chk values(5373484.21); commit; select * from chk; REM NUM REM ---------- REM 1 REM 12345 REM 0 REM .1 REM 12345.12 REM 9876543.21 COL TEXT format a110 select num, decode(num, 0,'ZERO', decode(instr(num,'.'), 1,'ZERO', to_char(to_date(substr(num,1,decode(instr(num,'.'),0,length(num),instr(num,'.') 1 ) ),'J' ),'JSP' ) ) ) || ' DOLLARS AND ' || decode(instr(num,'.'), 0,'ZERO', decode(length(num)-instr(num,'.'), 1,to_char(to_date(rpad(substr(num,instr(num,'.') +1,length(num) ),2,0 ),'J' ),'JSP' ), to_char(to_date(substr(num,instr(num,'.')+1,length(num) ),'J' ),'JSP' ) ) ) || ' CENTS ' TEXT from chk; REM NUM TEXT REM ---------- -------------------------------------------------------------------------------------------------------------- REM 1 ONE DOLLARS AND ZERO CENTS REM 12345 TWELVE THOUSAND THREE HUNDRED FORTY-FIVE DOLLARS AND ZERO CENTS REM 0 ZERO DOLLARS AND ZERO CENTS REM .1 ZERO DOLLARS AND TEN CENTS REM 12345.12 TWELVE THOUSAND THREE HUNDRED FORTY-FIVE DOLLARS AND TWELVE CENTS REM 5373484.21 FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR DOLLARS AND TWENTY-ONE CENTS drop table chk; Brian P. MacLean Oracle DBA, OCP8i Steven Lembark <lembark@wrkho To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> rs.com> cc: Sent by: Subject: Re: Convertion from Figures to Words [EMAIL PROTECTED] om 03/27/02 08:58 AM Please respond to ORACLE-L -- Ahmed Gholam Hussain <[EMAIL PROTECTED]> > Dear Listers , > > I need to convert Figures to Words ...EX : 123 to One Hundred Twenty > Three.... > Currently I am using a writen function to do that ..But it has > limitations ... > > Any suggestions regarding that ????? There are moduled which do this already in the Lingua:: group on CPAN. Hit up www.cpan.org, choose module search and look under that heading. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).