Re: Free Code Archive: Convert a Number to a String

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 04 Jun 1999 20:28:51 GMT
Message-ID: <375a178a.19513198@inet16.us.oracle.com>

On Fri, 04 Jun 1999 15:00:19 GMT, campbell_white_at_my-deja.com wrote:

>A new code utility has been added to the "Archives" section of the
>PL/SQL Pipeline. The PL/SQL Pipeline is a free internet
>community for PL/SQL developers worldwide. It is hosted by best-
>selling author Steven Feuerstein and sponsored by RevealNet.
>http://www.revealnet.com/pipeline.htm
>
>THE "TO_WORDS" FUNCTION
>
>Nitin Ghagare (nitin_ghagare_at_hotmail.com) has very generously
>allowed RevealNet to add to its archive his to_words function,
>which converts a number to a string of words describing that
>number. Format: sql file, 3K.

That's great but I think you can get the same result with the query...

select to_char( to_date(:N, 'J'), 'Jsp' ) from dual;

eg.
SQL> select to_char( to_date(123456, 'J'), 'Jsp' ) from dual;

TO_CHAR(TO_DATE(123456,'J'),'JSP')

---

One Hundred Twenty-Three Thousand Four Hundred Fifty-Six

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;

eg.

SQL> select decode( sign(-123), -1, 'Negative ', 0, 'Zero', NULL ) ||

  2         decode( sign( abs(-123) ),
  3                 +1, to_char( to_date( abs(-123),'J'),'Jsp') )

  4 from dual;

DECODE(SIGN(-123),-1,'NEGATIVE',0

---

Negative One Hundred Twenty-Three

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 04 1999 - 15:28:51 CDT