Display the number value in Words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))

from emp;

the output like,

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

--------- -----------------------------------------------------

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like, Rs. Three Thousand only.

SQL> select sal "Salary ",

(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))

"Sal in Words" from emp

Salary Sal in Words

------- ------------------------------------------------------

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

Interview Candidate
Aug 30th, 2005
19
16682

SQL

Answer

Showing Answers 1 - 19 of 19 Answers

Phani Josyula

Aug 30th, 2005

This query works only for the numbers between 1 and 5373484. What should we do if the number is out of this range?

kiran

Feb 7th, 2006

what do u mean by 'JSP' & why to_Date for a number ??

Mainak Aich

Mar 1st, 2006

Hi Kiran,
Here the 'j' used as a format in to_date function return the number in julian year.Whereas the 'jsp' used in to_char made the julian year spelled out.
i.e. j-julian year and sp-spelling so jsp.
Bye....

adarsh_sp
Profile Answers by adarsh_sp Questions by adarsh_sp

Oct 13th, 2006

Hi Phani Josyula
I think we cannot retrive numbers in words using a single querry other than julian option.... but we can create a function for this conversion ...
If any one knows another way of conversion using querry other than julian plz send me...

anil

Nov 10th, 2006

This query will also work,suppose if we want to convert 1920 into words,the query is select to_char(to_date(translate(1920,'1','1'),'j'),'jsp') from dual

Nikhil_4_Oracle

Mar 9th, 2007

HEY ALL,

Try out Using Array...

Tanx,

Nikhil

sweetytrivedi
Profile Answers by sweetytrivedi

Jun 25th, 2008

In order to display the number values , the best it can be done is with the julian i.e. j and jsp.

however, using the translate also returns the same value for whatever value to display.

select to_char(to_date(1920,'j'),'jsp') from dual;