測試1：

values TRIM(REPLACE(strip(replace(char(12.23), "0.", "#"),B,"0"),"#","0."));

RESULT:"12.23"

測試2：

values TRIM(REPLACE(strip(replace(char(1200.23), "0.", "#"),B,"0"),"#","0."));

RESULT:"1200.23"

測試3：

values TRIM(REPLACE(strip(replace(char(0.23), "0.", "#"),B,"0"),"#","0."));

RESULT:"0.23"

結論：此方法OK!

給個例子

db2 "drop table test"

db2 "create table test (c1 decimal(8,3))"

db2 "insert into test values (0.11),(1.01), (10.110), (101.01),1001.001)"

db2 "select * from test"

db2 -x "select c1,rtrim(char(bigint(c1)))||"."||translate(rtrim(translate((right(char(c1),4))," ","0")),"0"," ") from test"

固定保留小數點幾位

select cast(abc as decimal(10,3))

from 表名