SQL Plus - Display the number value in Words

GeekInterview.com > Interview Questions > Oracle > SQL Plus

Go To First | Previous Question | Next Question

SQL Plus | Question 41 of 132

Display the number value in Words

Total Answers and Comments: 8

Last Update: December 04, 2007

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

August 10, 2005 08:10:55

Vijay

RE: Display the number value in Words

select sal (to_char(to_date(sal 'j') 'jsp'))
from emp;

Is this answer useful? Yes | No

March 10, 2006 15:50:30

nagesh

RE: Display the number value in Words

Thank you.This has helped me a lot. But can any one explain how it works?

Thank you

Nagesh

Is this answer useful? Yes | No

September 20, 2006 11:56:02

deva

RE: Display the number value in Words

thanks vijay but deadly waiting for the explanation....

once again thanks for the good one.....

Is this answer useful? Yes | No

October 17, 2006 20:31:59

manideepika

RE: Display the number value in Words

The answer worked out...but can i get an explaination for this answer?

thanks

Is this answer useful? Yes | No

October 24, 2006 08:33:10

adarsh_sp

Member Since: October 2006 Contribution: 13

RE: Display the number value in Words

This is using ' julian' functionHere '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 it is ' jsp'. there is some limitations we cac use this only in numbers from 1 -5373484.I think we cannot retrive numbers in words using a single querry other than julian option....ie within the range 1 -5373484. 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...

Is this answer useful? Yes | No

October 24, 2006 08:34:35

RE: Display the number value in Words

Is this answer useful? Yes | No

September 11, 2007 03:03:36

Shruti Toggi

RE: Display the number value in Words

select to_char(to_date(123 'j' ) 'jsp') from dual;

eg:

SQL> select to_char(to_date(123 'j') 'jsp') from dual;

TO_CHAR(TO_DATE(123 'J')
------------------------
one hundred twenty-three

Is this answer useful? Yes | No

December 04, 2007 00:55:32

bhaskar1981buie

Member Since: September 2007 Contribution: 1

RE: Display the number value in Words

The number which have precesion can be spelled in words by this way..

SELECT DECODE(SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.')

0 11 DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 1 4)

'0000' '' TO_CHAR(TO_DATE('01-12-'||SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.')

0 11 DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 1 4) 'DD-MM-YYYY')

'YYYYSP')||' CRORE ')||

DECODE(SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.') 0 11

DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 5 2) '00' ''

TO_CHAR(TO_DATE('01-01-'||SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.')

0 11 DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 5 2) 'DD-MM-YY')

'YYSP')||' LAKH ')||

DECODE(SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.') 0 11

DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 7 2)

'00' '' TO_CHAR(TO_DATE('01-01'||SUBSTR(LPAD('1256932.1'

DECODE(INSTR('1256932.1' '.') 0 11 DECODE(LENGTH('1256932.1')-

INSTR('1256932.1' '.') 1 13 14)) '0') 7 2) 'DD-MM-YY') 'YYSP')||' THOUSAND ')||

DECODE(SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.') 0 11

DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 9 1) '0' ''

TO_CHAR(TO_DATE('01-'||SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.')

0 11 DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0')

9 1)||'-2006' 'DD-MM-YYYY') 'MMSP')||' HUNDREAD ')||

DECODE(SUBSTR(LPAD('1256932.1' DECODE(INSTR('1256932.1' '.') 0 11

DECODE(LENGTH('1256932.1')-INSTR('1256932.1' '.') 1 13 14)) '0') 10 2)

'00' '' TO_CHAR(TO_DATE('01-01'||SUBSTR(LPAD('1256932.1'

DECODE(INSTR('1256932.1' '.') 0 11 DECODE(LENGTH('1256932.1')-

INSTR('1256932.1' '.') 1 13 14)) '0') 10 2) 'DD-MM-YY') 'YYSP')||' RUPPES ')||

DECODE(INSTR('1256932.1' '.') 0 '' ' AND '||TO_CHAR(TO_DATE('01-01-'||

RPAD(SUBSTR('1256932.1' INSTR('1256932.1' '.')+1 2) 2 '0') 'DD-MM-YY') 'YYSP')||

' PAISE ONLY')

FROM DUAL

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank