Xref: alice comp.databases.oracle.misc:33579 comp.databases.oracle.server:53160 comp.databases.oracle.tools:26481
Path: alice!news-feed.fnsi.net!WCG!newsfeed.berkeley.edu!newsfeed.stanford.edu!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: numeric to fully-written-numbers pl/sql translator
Date: Mon, 14 Jun 1999 15:34:24 GMT
Organization: Oracle Service Industries
Lines: 45
Message-ID: <376e20e8.12358951@newshost.us.oracle.com>
References: <7k36da$glv$1@naxos.belnet.be>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: inet16.us.oracle.com 929374452 22855 138.1.123.116 (14 Jun 1999 15:34:12 GMT)
X-Complaints-To: usenet@inet16.us.oracle.com
NNTP-Posting-Date: 14 Jun 1999 15:34:12 GMT
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to "Guy Hendrickx" <Guy@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@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@us.oracle.com
Oracle Service Industries     Reston, VA   USA
-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation