decode numbers to words

Hi Friends,

I've read it here before a sql*plus program that decodes
numbers to words. I've searched all topics in this forums
but I can't find it anymore. Can somebody help me please...

Quote:

---

Originally posted by dvc
I've read it here before a sql*plus program that decodes
numbers to words.

---

Could you be more specific, how should I understand "decodes numbers to words"?

I want to decode the amount in a table
say 111.11 to "One Hundred Eleven Dollars and Eleven Cents". Had someone done this?

Quote:

---

Originally posted by dvc
I want to decode the amount in a table
say 111.11 to "One Hundred Eleven Dollars and Eleven Cents". Had someone done this?

---

:-) I could suggest at most this:

SQL> select to_char(111.11,'$99999.99') "Char" from dual;

Char
----------
$111.11

1 row selected.

patawa ka naman (thanks julian for being
attentive to my question)

Actually I want to develop a oracle report program
that prints checks. The requirement is that
the amount will also be printed in words.

Hi Hope u r looking for this

SQL>select to_char(to_date(111,'jsp'),'jsp') from dual;

TO_CHAR(TO_DATE(11
------------------
one hundred eleven

HTH


Regards
Venkat

yes! that's the one. Is JSP your function?
can you share it?

No, JSP is not my function. It is Oracle's. Jsp-Julian date in spelled out format.
Rgrds
Venkat

Thanks for the info! I didn't know that trick. It works on 8.1.7.

Thank you very much for giving the idea, Ven...

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
/