Monday, 4 February 2013

SQL- Convert Number Values Into Words


select ename,sal,to_char(to_date(sal,'J'), 'JSP') as converted_form
from scott.emp where rownum<=3; 

ENAME SAL     CONVERTED_FORM
--------------------------------------
SMITH   800            EIGHT HUNDRED
ALLEN   1600          ONE THOUSAND SIX HUNDRED
WARD   1250          ONE THOUSAND TWO HUNDRED FIFTY

If you have values less than 0 in a column then i will raise an error like
ORA-01854: julian date must be between 1 and 5373484.

ex:- in below query comm column has 0 value so it gives you the above error

select ename,comm,to_char(to_date(comm,'J'), 'JSP') as converted_form
from scott.emp ;

To avoid that modify your query like below:

select ename,sal,to_char(to_date(comm,'J'), 'JSP') as converted_form
from scott.emp where comm>0; 


Here 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 passed to to_date.


How To Convert A Number With Decimal To Words In Oracle

select to_char(to_date(floor(1234.99),'J'),'Jsp')||' and '||
to_char(to_date((1234.99-(floor(1234.99)))*100,'J'),'Jsp') from dual


If try to convert 999999999999.99  u would get an error because  julian date must be between 1 and 5373484

select to_char(to_date(floor(999999999999.99),'J'),'Jsp')||' point '||
to_char(to_date((999999999999.99-(floor(999999999999.99)))*100,'J'),'Jsp') from dual

go through this link to other possible solutions 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650