Convert Number (Digits) To Word (Strings)

There are many times when we need to spell out the amount in number to words. E.g. In banking/billing you need to convert amount 450 to "Four Hundred and Fifty" for display. This can be very lengthy task if you write a procedure to achieve this functionality. Here is a simple query (for ORACLE only) that will simplyfy your task. Suppose you want to convert 873 to words for displaying in your report.

SQL> select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

CONVERTED_FORM
---------------------------
EIGHT HUNDRED SEVENTY-THREE

J represents the julian format. When the to_char function is applied to the result of to_date, it spells (SP) the word for the number passwd to to_date.

To convert the decimal number simply follow the trick:

SQL> SELECT TO_CHAR(TO_DATE(trunc(100.50),'J'),'JSP') ' Point ' TO_CHAR(TO _DATE( to_number(substr('100.50', instr('100.50','.') + 1)),'J'),'JSP') from dual;