删除DB2中小数点后的尾随零 [英] Remove trailing zeros after decimal point in DB2

问题描述

我在DB2的标签表中有以下值（版本：9.5.8）

I have the following values in label table in DB2 (version : 9.5.8)

从带有ur的标签中选择字段4

1.5000
0.006
9.0001
104.2500
17.0000
3.5000

是否可以在更新查询中消除小数点后的零DB2？

如果小数点后所有数字均为零，那么我需要将其保留为 .0 。

Is it possible to eliminate the trailing zeros after the decimal point with an update query in DB2?
If all the digits are zeros after the decimal point, I need to keep that as .0, however.

预期输出：

1.5
0.006
9.0001
104.25
17.0
3.5

推荐答案

This is really hacky, but it should work for you.

SELECT
    trimmed || CASE WHEN LOCATE('.',trimmed) = LENGTH(trimmed) THEN '0' ELSE '' END
FROM (
    SELECT TRIM(TRAILING '0' FROM field4) AS trimmed
    FROM lablel
) A

Case 语句重新添加尾随零。如果您的字符串出于任何原因包含其他小数，这将中断。如果您使用的是DB2 9.7（我这里假设是LUW），那么更好的选择是使用 LOCATE_IN_STRING ，可以从头开始向后工作当您提供否定的开始时，输入字符串的值：

The CASE statement re-adds a trailing zero if a decimal is the last character. If your string contains other decimals for whatever reason, this would break. If you were on DB2 9.7 (I'm assuming LUW here), then a better option would be to use LOCATE_IN_STRING, which can work backwards from the end of the string when you provide a negative start:

SELECT
    trimmed || CASE WHEN LOCATE_IN_STRING(trimmed,'.',-1) = LENGTH(trimmed) THEN '0' ELSE '' END
FROM (
    SELECT TRIM(TRAILING '0' FROM field4) AS trimmed
    FROM lablel
) A

这篇关于删除DB2中小数点后的尾随零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！