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.

Showing Answers 1 - 9 of 9 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?

  Was this answer useful?  Yes

kiran

  • Feb 7th, 2006
 

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

  Was this answer useful?  Yes

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....

  Was this answer useful?  Yes

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...

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Nikhil_4_Oracle

  • Mar 9th, 2007
 

HEY ALL,

Try out Using Array...

Tanx,

Nikhil

  Was this answer useful?  Yes

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;

  Was this answer useful?  Yes

boobal

  • Mar 21st, 2012
 

This answer is no different to
select to_char(to_date(1290,j),jsp) from dual;

  Was this answer useful?  Yes

Talal Haider

  • Mar 16th, 2013
 

Can we convert a number in words without using direct function/method?

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions