As we all know We can convert number into words Using ‘Jsp’ format string in to_char function.
ind> select to_char(to_date(‘250′,’J’),’JSP’) from dual;
TO_CHAR(TO_DATE(‘
—————–
TWO HUNDRED FIFTY
1 row selected.
ind> select to_char(to_date(‘259′,’J’),’JSP’) from dual;
TO_CHAR(TO_DATE(‘259’,
———————-
TWO HUNDRED FIFTY-NINE
1 row selected.
ind> select to_char(to_date(‘9999′,’J’),’JSP’) from dual;
TO_CHAR(TO_DATE(‘9999′,’J’),’JSP’)
————————————–
NINE THOUSAND NINE HUNDRED NINETY-NINE
1 row selected.
But It has some limitations….See
ind> select to_char(to_date(‘250.25′,’J’),’JSP’) from dual;
select to_char(to_date(‘250.25′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ind> select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual;
select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
We can avoid this By creating one Function .
This is similar to Ask Tom Spell number
ind> select spell_number(‘9999999999.99’) from dual;
SPELL_NUMBER(‘9999999999.99’)
——————————————————————————–
nine billion nine hundred ninety-nine million nine hundred ninety-nine thousand
nine hundred ninety-nine
1 row selected.
ind> select into_rupees(‘9999999999.99’) from dual;
INTO_RUPEES(‘9999999999.99’)
——————————————————————————–
Nine Arab Ninety-Nine Crore Ninety-Nine Lakh Ninety-Nine Thousand Nine Hundred N
inety-Nine and Paise Ninety-Nine.
You can find this into_rupees script at Indian Rupees
Thank you 🙂