CODEWITHSUNDEEP
sqloracle-databaseoracle10g
contactmatt
contactmatt

Reputation: 18600

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

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 */

Upvotes: 59

Views: 158090

Answers (8)

Matheus Sant Ana
Matheus Sant Ana

Reputation: 690

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

Upvotes: 0

mkb
mkb

Reputation: 1155

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

Upvotes: 13

Tarkeshwar Prasad
Tarkeshwar Prasad

Reputation: 29

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

Upvotes: 0

Jo&#235;l
Jo&#235;l

Reputation: 11

Should work in all cases:

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

Upvotes: 1

DCookie
DCookie

Reputation: 43523

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)

Upvotes: 43

user6404490
user6404490

Reputation:

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);

Upvotes: 0

Vadzim
Vadzim

Reputation: 26170

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.

Upvotes: 58

rawberto
rawberto

Reputation: 83

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.

Upvotes: 6

Related Questions