谢谢大家 解决了
case when a.qty>0 then coalesce(replace(ltrim(replace(rtrim(char(decimal(Round(a.qty,1),12,1))),'0',' ')),' ','0'),'')
when a.qty<0 then '-' || coalesce(replace(ltrim(replace(rtrim(substr(char(decimal(Round(a.qty,1),12,1)),2,13)),'0',' ')),' ','0'),'')
else '0.0' end
1
CREATE FUNCTION DB2ADMIN.DECTOCHAR(X DECIMAL(31,2))
RETURNS VARCHAR(32)
LANGUAGE SQL
BEGIN atomic
DECLARE MYX VARCHAR(32);
SET MYX=CHAR(X);
WHILE LEFT(MYX,1)='0' DO
SET MYX=SUBSTR(MYX,2);
END WHILE;
RETURN MYX;
END
调用时的格式:
DECLARE MYDEC DECIMAL(8.2) DEFAULT 5.05;
DECLARE MYCHAR VARCHAR(9) DEFAULT NULL;
SET MYCHAR=DECTOCHAR(MYDEC);
输出的字符串为:5.05
2
select rtrim(char(int(col1)))||right(char(col1),POSSTR(char(col1),'.')) from tablename