返回小数点的Oracle格式掩码

只有当最后一个字符为零时,才能使用正则表达式删除它:

regexp_replace(to_char(<value>, '9999.999'), '0$', null)

演示:

with your_table (odds) as (
  select 10.50 from dual
  union all select 1.53 from dual
  union all select 1.004 from dual
  union all select 1200.00 from dual
)
select odds, regexp_replace(to_char(odds, '9999.999'), '0$', null) as formatted
from your_table;

      ODDS FORMATTED
---------- ---------
      10.5    10.50  
      1.53     1.53  
     1.004     1.004 
      1200  1200.00  

你可以使用普通的字符串函数,比如 instr 和 substr 但这里有点乱。

或者您可以使用case表达式来决定要使用的格式模型:

to_char(<value>,
  case when <value> = trunc(<value>, 2) then '9999.99'
       else '9999.999' end)

或

case when <value> = trunc(<value>, 2) then to_char(<value>, '9999.99')
     else to_char(<value>, '9999.999') end

或者其他的变种(例如,决定现在要包括很多9)。

演示:

with your_table (odds) as (
  select 10.50 from dual
  union all select 1.53 from dual
  union all select 1.004 from dual
  union all select 1200.00 from dual
)
select odds,
  case when odds = trunc(odds, 2) then to_char(odds, '9999.99')
       else to_char(odds, '9999.999')
  end as formatted
from your_table;

      ODDS FORMATTED
---------- ---------
      10.5    10.50 
      1.53     1.53 
     1.004     1.004
      1200  1200.00 

试试这个

Select to_char(12.5, rtrim(lpad(' ',length(trunc(12.5))+1, '9'))||'.'||rtrim(lpad(' ', case when length(12.5 - trunc(12.5)) <= 2 Then 3 else length(12.5 - trunc(12.5)) end, '0'))) from dual

注:将12.5改为任意数字。它将根据您的要求返回