GeekInterview.com
Series: Subject: Topic:
Question: 404 of 427

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.
Asked by: Interview Candidate | Asked on: Aug 30th, 2005
Showing Answers 1 - 9 of 9 Answers
Phani Josyula

Answered On : 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?

  
Login to rate this answer.
kiran

Answered On : Feb 7th, 2006

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

  
Login to rate this answer.
Mainak Aich

Answered On : 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....

  
Login to rate this answer.

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

  
Login to rate this answer.
anil

Answered On : 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

  
Login to rate this answer.
Nikhil_4_Oracle

Answered On : Mar 9th, 2007

HEY ALL,Try out Using Array...Tanx,Nikhil

  
Login to rate this answer.
sweetytrivedi

Answered On : Jun 25th, 2008

View all answers by sweetytrivedi

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;

  
Login to rate this answer.
boobal

Answered On : Mar 21st, 2012

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

  
Login to rate this answer.
Talal Haider

Answered On : Mar 16th, 2013

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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.