近日做DB2导入导出工作，decimal类型导出小有问题，前面会加+00000，好吧，这个么，导出的时候做下控制是可以解决的 modified by STRIPLZEROS DECPLUSBLANK.但是这样导出来，如果是整型的话，后面会带个”.”，这东东比较顽固，没办法限制掉，非常郁闷。平时decimal转varchar也会遇到点儿问题，索性就写个函数，直接转。具体如下：
CREATE FUNCTION DecimalToChar(d_Num decimal(31,5))
RETURNS VARCHAR(31)
RETURN
WITH Temp (ch) AS
(
SELECT
CASE WHEN d_Num=0 then ‘0’
WHEN SUBSTR(CHAR(d_Num),1,1)=’-‘ THEN
‘-‘||REPLACE(RTRIM(REPLACE(REPLACE(TRIM(REPLACE(REPLACE(CHAR(d_Num),’-‘,”),’0′,’ ‘)),’ ‘,’0′),’.’,’ ‘)),’ ‘,’.’)
ELSE
REPLACE(RTRIM(REPLACE(REPLACE(TRIM(REPLACE(CHAR(d_Num),’0′,’ ‘)),’ ‘,’0′),’.’,’
RETURNS VARCHAR(31)
RETURN
WITH Temp (ch) AS
(
SELECT
CASE WHEN d_Num=0 then ‘0’
WHEN SUBSTR(CHAR(d_Num),1,1)=’-‘ THEN
‘-‘||REPLACE(RTRIM(REPLACE(REPLACE(TRIM(REPLACE(REPLACE(CHAR(d_Num),’-‘,”),’0′,’ ‘)),’ ‘,’0′),’.’,’ ‘)),’ ‘,’.’)
ELSE
REPLACE(RTRIM(REPLACE(REPLACE(TRIM(REPLACE(CHAR(d_Num),’0′,’ ‘)),’ ‘,’0′),’.’,’