Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Convertion from Figures to Words

Re: Convertion from Figures to Words

From: <Brian_P_MacLean_at_eFunds.Com>
Date: Wed, 27 Mar 2002 08:58:36 -0800
Message-ID: <F001.00434F3E.20020327085836@fatcity.com>

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_at_wrkho       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>               
                    rs.com>              cc:                                                                               
                    Sent by:             Subject:     Re: Convertion from Figures to Words                                 
                    root_at_fatcity.c                                                                                         
                    om                                                                                                     
                                                                                                                           
                                                                                                                           
                    03/27/02 08:58                                                                                         
                    AM                                                                                                     
                    Please respond                                                                                         
                    to ORACLE-L                                                                                            
                                                                                                                           
                                                                                                                           






> 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: lembark_at_wrkhors.com

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: ListGuru_at_fatcity.com (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: Brian_P_MacLean_at_eFunds.Com

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: ListGuru_at_fatcity.com (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).
Received on Wed Mar 27 2002 - 10:58:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US