在博客上看到的一个写法,没有测过,先保存下来,考虑到这个写法不能处理负数,还得找其它方式
CREATE FUNCTION dec_char( DEC DOUBLE ) RETURNS VARCHAR(100) NO EXTERNAL ACTION
------------------------------------------------------------------------ -- SQL UDF (标量)
F1:
BEGIN
ATOMIC
DECLARE
car VARCHAR(100);
DECLARE
i INT;
SET i =0;
SET car=CHAR(DECIMAL(DEC,30,10));
--字符串左右填0,总长度为30位,小数点后10位
SET i=1;
--除去左边的0
WHILE SUBSTR(car,i,1)='0' DO SET i=i+1;
END WHILE;
--判断最左边的是否为'.'
IF SUBSTR(car,i,1)='.' THEN
SET i=i-1;
END IF;
SET car=SUBSTR(car,i);
--除去右边的0
SET i=1;
WHILE SUBSTR(car,LENGTH(car)-i,1)='0' DO SET i=i+1;
END WHILE;
SET car=SUBSTR(car,1,LENGTH(car)-i);
--判断最右边的是否为'.'
IF SUBSTR(car ,LENGTH(car),1)='.' THEN
SET car = SUBSTR(car,1,LENGTH(car)-1);
END IF;
RETURN car;
END
后面经过自己的组装,得到下面的写法,基本满足要求:
WITH
t AS
(
SELECT
RIDGING_WIDTH ori
FROM
FMS.T_LPB_TRANSPLANT
)
,
a AS
(
SELECT
ori,
CHAR(ori) ch,
trim( CHAR(INT(ori)) ) AS c1,--获取整数部分
SUBSTR(CHAR(ori), POSSTR (CHAR(ori),'.') + 1) AS c3 --获取小数部分
FROM
t
)
,
b AS
(
SELECT
ori,
ch,
c1,
c3,
TRANSLATE(RTRIM(TRANSLATE(c3, ' ', '0')), '0', ' ') AS c4--去掉小数右边多余的零
FROM
a
)
,
c AS
(
SELECT
ori ,
ch,
c1,
c3,
c4,
CASE
WHEN c4 =''
THEN c1
ELSE c1||'.' || c4
END c5 --如果转换后小数部分为空,则只显示整数
FROM
b
)
SELECT
ori "原始小数",
ch "原始字符串",
c1 "整数",
c3 "小数",
c4 "小数2",
c5 "拼装结果"
FROM
c

后来将上面的sql写成了一个自定义函数,以方便灵活调用
CREATE FUNCTION dec_char( v_dec DECIMAL(15,5) ) RETURNS VARCHAR(100) NO EXTERNAL ACTION
------------------------------------------------------------------------ -- SQL UDF (标量)
--将小数转换成字符串,去掉左边右边无意义的零,负数也可处理
--liyzh,2013-03-22
F1:
BEGIN
ATOMIC
DECLARE
c_int VARCHAR(100);
DECLARE
c_dec VARCHAR(100);
DECLARE
i INT;
SET c_int = trim( CHAR(INT(v_dec)) ) ;
--获取整数部分
SET c_dec = SUBSTR(CHAR(v_dec), POSSTR (CHAR(v_dec),'.') + 1);
--获取小数部分
SET c_dec = TRANSLATE(RTRIM(TRANSLATE(c_dec, ' ', '0')), '0', ' ') ;
--去掉小数右边多余的零
RETURN
CASE
WHEN c_dec ='' THEN
c_int
ELSE
c_int||'.' || c_dec
END;
--如果转换后小数部分为空,则只显示整数
END
