to_char() problem decimal points and thousand separator

Question

I'm trying to convert a number to a string and apply a format using to_Char in an Oracle database.

This is what I'm after (example of the value on the left and what I after on the left):

0                   --> 0
0.29                --> 0.29
25319,76            --> 25,319.76
12252,136456        --> 12,252.13

Best format I can come up with is this in SQL:

to_char(var, 'B99,999,999,999.09')

This is the result:

0                   --> nothing
0.29                --> .29
25319,76            --> 25,319.76
12252,136456        --> 12,252.13

So my issue is the 0.29 and 0. How can I get that part to work?

BR Kresten

Answer 1

The B format model:

Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).

And since you don't want a blank integer part then you probably don't want to use the B format model; instead, you want the FM format model that:

Returns a value with no leading or trailing blanks.

You can use RTRIM( TO_CHAR(var, 'FM99,999,999,990.99' ), '.' ).

For example:

SELECT var,
       RTRIM( TO_CHAR(var, 'FM99,999,999,990.99' ), '.' ) AS formatted_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( var ) AS
SELECT     0        FROM DUAL UNION ALL
SELECT     0.29     FROM DUAL UNION ALL
SELECT 25319.76     FROM DUAL UNION ALL
SELECT 12252.136456 FROM DUAL;

Outputs:

         VAR | FORMATTED_VALUE
-----------: | :--------------
           0 | 0              
         .29 | 0.29           
    25319.76 | 25,319.76      
12252.136456 | 12,252.14      

db<>fiddle here

Answer 2

You probably want to use 0 in the format before the decimal point.

Also you can add FM prefix instead of B to get rid of leading spaces and trailing zeroes.

After that you can use rtrim(..., '.') to remove the trailing decimal point.

(see Oracle Format Models documentation)

select v, rtrim(to_char(v, 'FM99,999,999,990.99'), '.') from
  (select 0 as v from dual
   union all select 0.29 from dual
   union all select 25319.76 from dual
   union all select 12252.136456 from dual
  ) t
;  

/* output:
           0    0
         .29    0.29
    25319.76    25,319.76
12252.136456    12,252.14
*/