Hi All,
I am on Oracle 10.2 on Windows server.
I never thought that, this can be difficult, but probably I was over-confident.
I want to format numeric values in a way that, 1) if the value is 0, it should appear zero (no decimal point) 2) if the number has decimal point, then I need to return value with 2 decimal places 3) If the value has no decimal places, display the whole number (possibly no decimal point and zeros in the number).
Tried few options
SQL> select to_char( 123, '9999.99') from dual ;
TO_CHAR(
--------
123.00
SQL> select to_char( 123, '9999.00') from dual ;
TO_CHAR(
--------
123.00
SQL> select to_char( 0, '9999.00') from dual ;
TO_CHAR(
--------
.00
SQL> select to_char( 0, '0000.00') from dual ;
TO_CHAR(
--------
0000.00
SQL> select to_char( 21, '0000.00') from dual ;
TO_CHAR(
--------
0021.00
SQL> select to_char( 21, '9999.00') from dual ;
TO_CHAR(
--------
21.00
Can anybody suggest a single format string which can satisfy all three conditions I mentioned above ?
Thanks in advance