Re: numeric to fully-written-numbers pl/sql translator

From: Kelly Young <young_at_maricopa.edu>
Date: Mon, 14 Jun 1999 09:06:34 -0700
Message-ID: <3765288A.55B0@maricopa.edu>

Guy Hendrickx wrote:
>
> Hi,
> I'm looking for a pl/sql procedure which is capable of translating
> a numeric value to a full-text string :
> ex. '20' will become 'twenty'.
> If possible it should be available for Ducth, French and English.
> If only one of the above languages is supported, I'm still interested.
> Kind regards,
> Guy Hendrickx
> Guy_at_Cereus.BE

Here's an example of printing dollars and cents in English words. Hope this helps.
/*

   Oracle SQL example of converting numbers to words. Given a table called "CHK" with a single column "NUM" - number(7,2):

    NUM

---

    .34
1234.45

   3445

ZERO DOLLARS AND THIRTY-FOUR CENTS
ONE THOUSAND TWO HUNDRED THIRTY-FOUR DOLLARS AND FORTY-FIVE CENTS THREE THOUSAND FOUR HUNDRED FORTY-FIVE DOLLARS AND ZERO CENTS */
select 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(length(num)-instr(num,'.'),
              1,to_char(to_date(rpad(substr(num,instr(num,'.')+1,
                                            length(num)),
                                      2,0),
                                'J'),
                         'JSP'),
              decode(instr(num,'.'),
                     0,'ZERO',
                    
to_char(to_date(substr(num,instr(num,'.')+1,length(num)),
                                     'J'),
                             'JSP')))

   ||' CENTS '
   from chk;
--
Kelly Young
Maricopa Community Colleges
young_at_maricopa.edu Received on Mon Jun 14 1999 - 11:06:34 CDT