decode numbers to words

There are some limitations in using this trick that you must be avare of. Take a look at http://www.dbasupport.com/forums/sho...threadid=11319

Also (although probably not so inportant now in the era of 8i and 9i), there was a bug in 7.2.x I think where some integers were not spelled correctly.

Moved from administration forum to the development forum. As this question would be more appropriate under this forum

Sam

You cannot use JSP for numbers
greater than 5373484

SQL> select to_char( to_date(5373485 ,'J'),'Jsp') from dual
2 /
ERROR:
ORA-01854: julian date must be between 1 and 5373484

Ofcourse you can overcome this using a small pl/sql function using the 'jsp' format.

Hi csvenkata

What if we have 111.56 ?

Quote:

---

Originally posted by aph
Hi csvenkata

What if we have 111.56 ?

---

Julian dates in Oracle are allways represented by integers (they represent only the date portion without the time), so you must convert your decimal numbers into two integers (before and after the decimal point) and process them separately. Note that the JSP trick does not work with 0. If the number can be negative, you must use ABS() function and process the sign separately, like:

Code:

---

SQL> set verify off
SQL> undefine test_number
SQL> SELECT DECODE(SIGN(&&test_number), -1, 'MINUS ') ||
  2        DECODE(TRUNC(&&test_number), 0, 'ZERO',
  3                TO_CHAR(TO_DATE(ABS(TRUNC(&&test_number)) ,'J'),'JSP')) ||
  4        ' DOLLARS, ' ||
  5        DECODE(MOD(ABS(&&test_number), 1), 0, 'ZERO',
  6                TO_CHAR( TO_DATE(MOD(ABS(&&test_number), 1)*100 ,'J'),'JSP')) ||
  7        ' CENTS' spelled
  8  FROM dual;
Enter value for test_number: 0

SPELLED
--------------------------------------------------------------------------------
ZERO DOLLARS, ZERO CENTS

SQL> undefine test_number
SQL> /
Enter value for test_number: 111.56

SPELLED
-------------------------------------------
ONE HUNDRED ELEVEN DOLLARS, FIFTY-SIX CENTS

SQL> undefine test_number
SQL> /
Enter value for test_number: 9999

SPELLED
--------------------------------------------------------------------------------
NINE THOUSAND NINE HUNDRED NINETY-NINE DOLLARS, ZERO CENTS

SQL> undefine test_number
SQL> /
Enter value for test_number: -111.56

SPELLED
-------------------------------------------------
MINUS ONE HUNDRED ELEVEN DOLLARS, FIFTY-SIX CENTS

SQL> undefine test_number
SQL> /
Enter value for test_number: 15

SPELLED
--------------------------------------------------------------------------------
FIFTEEN DOLLARS, ZERO CENTS

SQL> undefine test_number
SQL> /
Enter value for test_number: 0.99

SPELLED
--------------------------------------------------------------------------------
ZERO DOLLARS, NINETY-NINE CENTS

---

Can I share my Code

select to_char(to_Date(substr(&&mynum,1,instr(&&mynum,'.')-1),'j'),'jsp') || ' POINT ' ||
to_char(to_Date(substr(&&mynum,instr(&&mynum,'.')+1,length(&&mynum)),'j'),'jsp')
from dual
/