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

Question

In Oracle, when converting a number with a leading zero to a character, why does the leading number disappear? Is this logic Oracle specific, or specific to SQL?

Example:

SELECT TO_CHAR(0.56) FROM DUAL;
/* Result = .56 */

score 50 · Answer 1 · edited May 23 '17 at 12:03

I was looking for a way to format numbers without leading or trailing spaces, periods, zeros (except one leading zero for numbers less than 1 that should be present).

This is frustrating that such most usual formatting can't be easily achieved in Oracle.

Even Tom Kyte only suggested long complicated workaround like this:

case when trunc(x)=x
    then to_char(x, 'FM999999999999999999')
    else to_char(x, 'FM999999999999999.99')
end x

But I was able to find shorter solution that mentions the value only once:

rtrim(to_char(x, 'FM999999999999990.99'), '.')

This works as expected for all possible values:

select 
    to_char(num, 'FM99.99') wrong_leading_period,
    to_char(num, 'FM90.99') wrong_trailing_period,
    rtrim(to_char(num, 'FM90.99'), '.') correct
from (
  select num from (select 0.25 c1, 0.1 c2, 1.2 c3, 13 c4, -70 c5 from dual)
  unpivot (num for dummy in (c1, c2, c3, c4, c5))
) sampledata;

    | WRONG_LEADING_PERIOD | WRONG_TRAILING_PERIOD | CORRECT |
    |----------------------|-----------------------|---------|
    |                  .25 |                  0.25 |    0.25 |
    |                   .1 |                   0.1 |     0.1 |
    |                  1.2 |                   1.2 |     1.2 |
    |                  13. |                   13. |      13 |
    |                 -70. |                  -70. |     -70 |

Still looking for even shorter solution.

There is a shortening approarch with custom helper function:

create or replace function str(num in number) return varchar2
as
begin
    return rtrim(to_char(num, 'FM999999999999990.99'), '.');
end;

But custom pl/sql functions have significant performace overhead that is not suitable for heavy queries.

To get more decimal places and custom delimiter, I used `RTRIM(TO_CHAR(x, 'FM999999999999990D99999999999999', 'NLS_NUMERIC_CHARACTERS = '',.'''), ',')`. This is the format used in Czech locale. — Palec, Dec 21 '16 at 14:39
@Palec can you explain the character and the meaning of NLS_NUMERIC_CHARACTERS = '',.''' ? — Francesco Pegoraro, Oct 23 '20 at 16:38
@FrancescoPegoraro, they are decimal and thousands separator, in this order. 1.000,000 would read as a thousand with three decimal places in my country, but as a one with six decimal places in English-speaking countries. https://datacadamia.com/db/oracle/nls_numeric_characters — Palec, Oct 26 '20 at 12:02
For money this works: `ltrim(to_char('45587.25', '$999,990.99')` or `ltrim(to_char('0', '$999,990.99')` — jpell, Mar 27 '23 at 12:40

score 43 · Accepted Answer · edited Oct 29 '18 at 12:35

43

It's the default formatting that Oracle provides. If you want leading zeros on output, you'll need to explicitly provide the format. Use:

SELECT TO_CHAR(0.56,'0.99') FROM DUAL;

or even:

SELECT TO_CHAR(.56,'0.99') FROM DUAL;

The same is true for trailing zeros:

SQL> SELECT TO_CHAR(.56,'0.990') val FROM DUAL;

VAL
------
 0.560

The general form of the TO_CHAR conversion function is:

TO_CHAR(number, format)

edited Oct 29 '18 at 12:35

Magnus Reftel

967
6
19

answered Jul 14 '11 at 15:20

DCookie

42,630
11
83
92

5

check out http://ss64.com/ora/syntax-numfmt.html for a good list on the different number format available – jworrin Jul 14 '11 at 15:22
12

The problem with this solution comes when we don't have the exact number of digits after the decimal point. What can we do in this case? A very ugly solution would be to add a `case when myNum < 1 then addLeadingZero end case` and do the same with numbers between -1 and 0. So... what can we do? – Edu Castrillon Apr 02 '14 at 09:44
4

For the sake of my previous comment (more than 3 years ago), I just have to add this: rtrim(to_char('.45', '0.9999999999999'), '0') will return '0.45', which is what we were looking for at the beginning – Edu Castrillon Sep 25 '17 at 13:29
2

This answer is too specific (for one particular case) and should not be the top upvoted. For example, answers by @Vadzim and others cover both the OP's case, and various other for larger numerals. – runr Jun 13 '19 at 12:31
1

@EduCastrillon that's exactly what I was looking for, thanks! – atamata Oct 30 '19 at 12:00

mkb · Answer 3 · 2020-05-28T07:44:26.203

13

Seems like the only way to get decimal in a pretty (for me) form requires some ridiculous code.

The only solution I got so far:

CASE WHEN xy>0 and xy<1 then '0' || to_char(xy) else to_char(xy)

xy is a decimal.

xy             query result
0.8            0.8  --not sth like .80
10             10  --not sth like 10.00

edited May 28 '20 at 07:44

answered Oct 22 '14 at 12:48

mkb

1,106
1
18
21

This is actually the best solution. `CASE WHEN xy>0 and xy<1 then '0' || xy else xy||'' end xy` – xaiweiyi Mar 22 '23 at 08:56

rawberto · Answer 4 · 2014-05-21T12:43:17.367

That only works for numbers less than 1.

select to_char(12.34, '0D99') from dual;
-- Result: #####

This won't work.

You could do something like this but this results in leading whitespaces:

select to_char(12.34, '999990D99') from dual;
-- Result: '     12,34'

Ultimately, you could add a TRIM to get rid of the whitespaces again but I wouldn't consider that a proper solution either...

select trim(to_char(12.34, '999990D99')) from dual;
-- Result: 12,34

Again, this will only work for numbers with 6 digits max.

Edit: I wanted to add this as a comment on DCookie's suggestion but I can't.

score 0 · Answer 5 · answered May 31 '16 at 11:47

0

Try this to avoid to_char limitations:

SELECT 
regexp_replace(regexp_replace(n,'^-\'||s,'-0'||s),'^\'||s,'0'||s)
FROM (SELECT -0.89 n,RTrim(1/2,5) s FROM dual);

answered May 31 '16 at 11:47

1

using two regex_replace look very suboptimal to me. And original question is rather Why?, not How? – J. Chomel May 31 '16 at 11:57
2

That's better than to_char, because you are not limited to specific format. You escape ####### situation for big numbers, and you escape undesired zeros after decimal for whole number. It just adds zero for [-1;1] range numbers that looks awkward in native Oracle representation. It's enought just one regexp_replace, if you're sure that the number is positive. – May 31 '16 at 12:35

score 0 · Answer 6 · edited Aug 14 '19 at 12:04

0

Should work in all cases:

SELECT regexp_replace(0.1234, '^(-?)([.,])', '\10\2') FROM dual

edited Aug 14 '19 at 12:04

4b0

21,981
30
95
142

answered Aug 14 '19 at 12:03

Joël

1

Tarkeshwar Prasad · Answer 7 · 2020-05-06T02:22:30.053

0

Below format try if number is like

ex 1 suppose number like 10.1 if apply below format it will be come as 10.10

ex 2 suppose number like .02 if apply below format it will be come as 0.02

ex 3 suppose number like 0.2 if apply below format it will be come as 0.20

to_char(round(to_number(column_name)/10000000,2),'999999999990D99') as column_name

edited May 06 '20 at 02:22

answered May 05 '20 at 15:13

Tarkeshwar Prasad

19
3

score 0 · Answer 8 · answered Dec 01 '22 at 23:45

If your country supports comma for decimal, use this:

SELECT regexp_replace(
            regexp_replace(to_char(YOUR_NUMBER), '^,', '0,'), -- Positives
            '^-,', '-0,') -- Negatives

Otherwise, if your country supports dots for decimal, use this:

SELECT regexp_replace(
            regexp_replace(to_char(YOUR_NUMBER), '^.', '0.'), -- Positives
            '^-.', '-0.') -- Negatives

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

8 Answers8

Linked