with num as (
select 999.815 as n from dual
union all
select 999 as n from dual
union all
select 0.9 as n from dual
union all
select 0 as n from dual
)
SELECT n
, TRUNC(n) as trc
, SIGN(n - TRUNC(n)) as shn
, TO_CHAR(n, DECODE(SIGN(n - TRUNC(n)), 0, 'FM999', 'FM990.999')) AS formatted_string
FROM num
输出:
| N | TRC | SHN | FORMATTED_STRING |
|---|---|---|---|
| 999.815 | 999 | 1 | 999.815 |
| 999 | 999 | 0 | 999 |
| 0.9 | 0 | 1 | 0.9 |
| 0 | 0 | 0 | 0 |
