Oracle - Why does the leading zero of a number disappear when converting it TO_CHAR

Fine-tuning with format models

When dealing with various formatting requirements, a simple FM might not do the job. You may need to bring out the big guns, and for some cases, even modify multiple parameters. For instance, to get rid of trailing spaces, periods, or surplus zeros, consider this:

SELECT RTRIM(TO_CHAR(0.7, 'FM999999999999990.99'), '.') FROM dual; -- No excess baggage, please! Outputs '0.7'


This function chain eliminates trailing decimal points left from the FM format. However, steer clear of such methods when dealing with large queries, where every function adds to the performance overhead. Resort to such specificity when accuracy in a manageable dataset is the holy grail.

Shooting straight with format models

When the rendezvous is with precision and specificity, none can beat direct format models:

SELECT TO_CHAR(0.05, '0D00') FROM dual; -- Champions the underdog. Ensures leading zero for <1 numbers


The '0D00' syntax brightens the day for numbers under 1 by safely bringing home the leading zero. If you are dealing with complex formatting situations where Tom Kyte's methods might help but seem too tedious, consider using constructs like CASE or mere concatenation:

SELECT CASE WHEN my_number < 1 THEN '0' || TO_CHAR(my_number, 'FM.99')
            ELSE TO_CHAR(my_number, 'FM999990D99')
            END
FROM dual; -- The switch trick. Returns formatted numbers with leading zero for <1 numbers


Here a '0' is prepended for numbers between 0 and 1, and a more normalized format is maintained for others.

Performance prospects with custom functions

While custom PL/SQL functions might seem like the sacred sword for formatting, they could cost you a dragon — performance overhead. Therefore, always judge the indispensability of such functions against the severity of data precision required and the constraints of your environment.

Preparing for unpredictability

Oracle's TO_CHAR behavior may bring forth some unexpected scenarios, especially in comparison to other SQL-based systems. Make sure to try different format models against edge cases in your specific requirements. It's critical to verify and validate TO_CHAR behavior with different numbers and formats, knowing that a one-size-fits-all solution may not always exist.

Simplifying complexity with alternatives

If impending complexity scares you away from concatenated format models or custom functions, basic SQL solutions may be your refuge:

SELECT '0' || TRIM(LEADING '0' FROM TO_CHAR(007, '999')) FROM dual; -- When in doubt, add '0'!


This alternative is simpler but be aware, it might not fit all situations. Choose your path wisely and consider maintenance overhead, code readability, and comprehensibility for other developers who might stumble upon your work.