Oracle to_char function with a number format that has an arbitrary precision and certain scale

Question

This one is pretty simple actually yet I wasn't able to find anything useful.

In my SQL query I have some rounded numbers with a single scale value - round(number,1). If the numbers are rounded to some decimal digit it prints in the format '9,9'.

On the other hand if the numbers are rounded to an integer, only the integer value without the zero after comma is printed although I want my query to select the numbers in '9,9' format even the decimal digit is zero.

In short, I think I need something like for example

to_char((select round(121.01,1), from dual), '*,1') ; to output 121,0.

What is the best way to do this? Thanks in advance

Korhan

Solution 1

All you have to do is specify the number of decimal points you want in your to_char. The problem with using format masks is that you need to specify the number of numbers you want in front of your decimal point.

SQL> select to_char(round(121.01,1),'999.9') from dual;

TO_CHA
------
 121.0

SQL> select to_char(round(121.4,1),'999.9') from dual;

TO_CHA
------
 121.4

SQL> select to_char(round(121,1),'999.9') from dual;

TO_CHA
------
 121.0

SQL> select to_char(round(5121,1),'999.9') from dual;

TO_CHA
------
######

SQL>

There are a number of other formatting options.

Solution 2

Use 0 instead 9 for decimal places:

SELECT TO_CHAR( ROUND( 121.01, 1 ), '990D0' ) num FROM DUAL;

NUM  
------
121.0

Solution 3

This simple query may help you,

select to_char(round(121.01,1), '999.0') from dual;

In to_char function:

9 - indicate to block/hide zeros in the output.

0 - indicate to show zero in the output at anywhere in before/after decimal point.

Note:

No. of '9/0's in before/after decimal point is number of digits which you want to display beore/after decimal point.

Comments

[Szilard Barany]

If you want to display numbers with arbitrary precision (but with fixed scale) nicely, you can use this format mask: 'FM9G999G999G999G990D0' (or even longer).