Re: Convert Numbers to Words with SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 May 1998 01:11:01 GMT
Message-ID: <356cb968.1625086@192.86.155.100>

On Wed, 27 May 1998 12:10:19 -0500, you wrote:

>GHouck wrote:
>
>> A while ago there was a snippet in one of these Oracle
>> newsgroups illustrating a way to display numbers as
>> full (English) words, using SQL and the Date/Char
>> functions.
>>

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
/        

>> It created a function that took a number as input,
>> listing the words as output; that is:
>>
>> select f(123.4) from ...
>>
>> yielded:
>>
>> 'one hundred twenty three point four'
>>
>> Does anyone have this example? Or one like it? Or knows
>> where it could be located (without having to start from
>> scratch, that is)?
>>
>> Thanks,
>>
>> Geoff Houck
>> systems hk
>> hksys_at_teleport.com
>> http://www.teleport.com/~hksys
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 27 1998 - 20:11:01 CDT