with t1 as ( select '123.4' c1 from dual union all select '123.55' c1 from dual union all select '123.24' c1 from dual union all select '123' c1 from dual union all select '123.6' c1 from dual )select case when length(c1)-instr(c1,'.') = length(c1) then c1||'.00' when length(c1)-instr(c1,'.') = 1 then c1||'0' else c1 end c1 from t1 c1 ----------------------- 1 123.40 2 123.55 3 123.24 4 123.00 5 123.60
如果每个值都有1个或者2个小数 那直接判断就行了 with t1 as ( select '123.4' c1 from dual union all select '123.55' c1 from dual union all select '123.24' c1 from dual union all select '123.6' c1 from dual )select decode(length(c1)-instr(c1,'.'),1,c1||'0',c1) c1 from t1 c1 -------------------- 1 123.40 2 123.55 3 123.24 4 123.60
WITH T1 AS (SELECT '123.4' C1 FROM DUAL UNION ALL SELECT '123.551' C1 FROM DUAL UNION ALL SELECT '123.24' C1 FROM DUAL UNION ALL SELECT '123.6' C1 FROM DUAL) SELECT DECODE ( LENGTH(t1.c1),5, RPAD(T1.C1, 6, 0),t1.c1 ) FROM T1 T1
select TO_CHAR('123','fm99999999990.00') from dual; 这个应该是可以解决所有你想要的问题~~
select TO_CHAR('123','fm99999999990.00') from dual;
13:18:33 SCOTT@myora > select to_char(to_number(to_char(123.4)||'0'),'999D99') from dual;TO_CHAR(TO_NUM
--------------
123.40已选择 1 行。
我就是用的to_char,但这个字段必须是数字型,所以我用to_number转换,0就不见了
to_number(to_char(SUM(AMO),'9999999999999999999999999999999D99')) AMO1
我就是用的to_char,但这个字段必须是数字型,所以我用to_number转换,0就不见了
to_number(to_char(SUM(AMO),'9999999999999999999999999999999D99')) AMO1
with t1 as
(
select '123.4' c1 from dual union all
select '123.55' c1 from dual union all
select '123.24' c1 from dual union all
select '123' c1 from dual union all
select '123.6' c1 from dual
)select case when length(c1)-instr(c1,'.') = length(c1) then c1||'.00'
when length(c1)-instr(c1,'.') = 1 then c1||'0'
else c1 end c1
from t1 c1
-----------------------
1 123.40
2 123.55
3 123.24
4 123.00
5 123.60
with t1 as
(
select '123.4' c1 from dual union all
select '123.55' c1 from dual union all
select '123.24' c1 from dual union all
select '123.6' c1 from dual
)select decode(length(c1)-instr(c1,'.'),1,c1||'0',c1) c1
from t1 c1
--------------------
1 123.40
2 123.55
3 123.24
4 123.60
(SELECT '123.4' C1
FROM DUAL
UNION ALL
SELECT '123.551' C1
FROM DUAL
UNION ALL
SELECT '123.24' C1
FROM DUAL
UNION ALL
SELECT '123.6' C1
FROM DUAL)
SELECT DECODE ( LENGTH(t1.c1),5, RPAD(T1.C1, 6, 0),t1.c1 ) FROM T1 T1
这个应该是可以解决所有你想要的问题~~