Using PL SQL, I need to format number to VARCHAR2. I need to show decimal places if they are not zero. And decimal separator should be comma.

CodePudding user response：

Would something like this do?

SQL> with test (input) as
  2    (select   0.2 from dual union all
  3     select 100.4 from dual union all
  4     select  22   from dual
  5    )
  6  select input,
  7         case when input = trunc(input) then to_char(input, '999G990')
  8              else to_char(input, '999G990D0')
  9         end output
 10  from test;

     INPUT OUTPUT
---------- ----------
        ,2        0,2
     100,4      100,4
        22       22

SQL>

CodePudding user response：

this is locale independent, does not limit or round numbers

with
  test(input) as (
    select   0.2      from dual union all
    select 100.4      from dual union all
    select -12.34567  from dual union all
    select   1.23e34  from dual union all
    select  -1.23e-34 from dual union all
    select  22        from dual
  )
select input
      ,case when input < 0 then '-' end -- sign
      ||case when abs(input) < 1 then '0' end -- zero
      ||replace(to_char(abs(input)),to_char(0,'fmd'),',') -- comma
       output
  from test
/