October 28, 2009

Interesting query to spell numbers in Oracle

I was surfing through tkyte's blog and found this query to spell the numbers in oracle.

SQL> select to_char(to_date(19994,'j'),'jsp') from dual;
TO_CHAR(TO_DATE(19994,'J'),'JSP')
------------------------------------------
nineteen thousand nine hundred ninety-four
Lets look little close to this.
inner query: select to_date(19994,'J') from dual;
It is basically used to convert number to date and 'J' signifies Julian date format.
outer query: select to_char(),'JSP') from dual; 
It is SPELLING out Julian date. J-SP means Julian Spell.


The above query may fail for very large numbers. So a better solution by Tom is here.