Hi guys,
What is the best way to prevent oracle from suppressing leading 0 while convert a number to char. I am using Release 11.2.0.1.0.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(0.01) from dual;
TO_
---
.01
I can achieve this by using decode, for example:
SQL> select decode(instr(to_char(0.01), '.'),
2 1,
3 '0' || to_char(0.01),
4 to_char(0.01)) val
5 from dual;
VAL
----
0.01
SQL>
But it doesn't look very pretty, is there a better way to do this please? Please note my data is mixed with values with or without decimal places. When the value is greater equal than 1, i want to have the output as same as to_char(val), the padding zero only applies to when the value is between 0 to 1. Many thanks.
Best regards,
Pete