I have salary 800 and want to output as eight hundred which function is helpful for this in Oracle?

by srikanth
(hyderabad)

I AM HAVING SALARY 800 IN MY EMP TABLE I WANT TO DISPLAY THE OUTPUT LIKE THIS EIGHT HUNDRED WHICH FUNCTION IS USEFUL FOR THIS

Comments for I have salary 800 and want to output as eight hundred which function is helpful for this in Oracle?

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 25, 2015
Rating
starstarstarstarstar
Converting numbers to words in SQL
by: Anonymous

There is a very quick and easy way to do this for numbers between 1 and 5373484 - just convert the number to a Julian date and then convert back to text and spell it out.

To convert 800 to eight hundred then the SQL code is:
SELECT TO_CHAR (TO_DATE (800, 'j'), 'jsp') FROM DUAL;

That gives the following result:

"SAL"
eight hundred

If you wnat the words capitalised just use a cpaital J as the parameter for the to_char function.

SELECT TO_CHAR (TO_DATE (2839, 'j'), 'Jsp') FROM DUAL;

That gives the following result:

"SAL"
Two Thousand Eight Hundred Thirty-Nine

WHat we're doing in this code is converting the number to a Julian date (which must be between 1 and 5373484) and then converting it to a character string with the date spelled out as an offset in days from 1st January 4713 BC (day 1 in the Julian calendar).

To use this for negtaive numbers you would have to use the abs function (to get the absolute value) and the sign function to determine whther the number is positive or negative. You would end up with something like this:

SELECT DECODE(SIGN(my_number),-1,'Minus ',null)||TO_CHAR (TO_DATE (ABS(my_number), 'j'), 'Jsp') FROM DUAL;

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.