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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 14 Jun 1999 15:34:24 GMT
Message-ID: <376e20e8.12358951@newshost.us.oracle.com>

A copy of this was sent to "Guy Hendrickx" <Guy_at_Cereus.BE> (if that email address didn't require changing) On Mon, 14 Jun 1999 17:20:12 +0200, you 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
>

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

  1* select to_char( to_date(5373484,'J'),'Jsp') from dual SQL> /   TO_CHAR(TO_DATE(5373484,'J'),'JSP')

---

Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four  

This will work for numbers between 1 and 5,373,484...

You can take it a step further to support numbers -5,373,484 .. 5,373,484 by

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

       decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') ) from dual
/

should be international already (language support)

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 14 1999 - 10:34:24 CDT