OracleConnections

Oracle Community Network

FORMAT A NUMBER IN ORACLE

I'm trying to change a char value to a number. So, I have used the function called To_Number

But, my question is why is the TO_Number function sometimes suppressing zeros?.

For example..

select TO_NUMBER ('1000.10')
 from dual;

I'm getting the result like 1000.1 but I'm expecting the result should be 1000.10. I'll appreciate your help, if you give any kind of hints or suggestions to get the above result.

7 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Sharonda Seevers on January 13, 2014 at 5:23pm

The TO_NUMBER function will drop all ending 0's after the decimal place. Changing the format mask of the TO_NUMBER function will not prevent the suppression of ending zeros.

▶ Reply

Permalink Reply by Pearlene Prum on January 13, 2014 at 5:25pm

select TO_NUMBER ('1000.10', '9999.99')
 from dual;

The above will still return 1000.1

If you wish to perform mathematical operations on the value, we'd recommend using the TO_Number function to convert your value to a number. But if you wish to display the number with a certain format, you can either use the TO_Char function.