Re: FUNCTION in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/18
Message-ID: <33a826a7.13973623@newshost>#1/1

On Mon, 16 Jun 1997 11:30:57 +0100, "Acácio Nuno Loureiro de Jesus" <i19007_at_caravela.di.fc.ul.pt> wrote:

>Hi!
> If you have a function in PL/SQL that converts a number into it's
>designation like
>for example : 1 returns 'one'
> 10 returns 'ten'
> 234 returns 'two hundred and thirty four'
>Please send me.
> Thanks!
>
>Acácio.
>

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
/          

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 18 1997 - 00:00:00 CDT