---

Oracle / PLSQL: Format a number in Oracle

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

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

For example,

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.

OR you can try applying a format mask in whatever reporting tool that you are using.

Learn more about the TO_NUMBER function.

Learn more about the TO_CHAR function.