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...
Printable View
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...
Could you be more specific, how should I understand "decodes numbers to words"?Quote:
Originally posted by dvc
I've read it here before a sql*plus program that 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?
:-) I could suggest at most 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?
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 ?
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:Quote:
Originally posted by aph
Hi csvenkata
What if we have 111.56 ?
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
/