SQL db2 中 Varchar 到 Decimal 的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23227240/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Varchar to Decimal conversion in db2
提问by Rikin
I am trying to convert a varcharfield to decimalfield in db2but I am getting an error
我正在尝试将varchar字段转换为decimal字段，db2但出现错误
AMOUNTfield is a varcharfield which needs to be converted to decimal(31,3)
AMOUNTfield 是varchar需要转换为的字段decimal(31,3)
Given below is my query
下面给出的是我的查询
SELECT CAST(ROUND(TRIM(AMOUNT),3) AS DECIMAL(31,3))
FROM TABLENAME
Note: AMOUNT field (varchar)has a NULLvalue as well
Sample values:
注意：AMOUNT field (varchar)也有一个NULL值
示例值：
7.324088
-42.97209
854
NULL
6
6
350
-6
15.380584
1900
I get the below error:
我收到以下错误：
Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018
在函数“DECFLOAT”的字符串参数中发现无效字符。SQLSTATE=22018
回答by Rahul
Try doing like
尝试这样做
SELECT CAST(ROUND(COALESCE(TRIM(AMOUNT),0),3) AS DECIMAL(31,3))
FROM TABLENAME
回答by AngocA
Are you sure your input values correspond with the same locale. I mean is the minus sign a minus or the MS Word minus? What about the decimal separator? In some languages there is a comma instead of a dot.
您确定您的输入值对应于相同的语言环境。我的意思是减号是减号还是 MS Word 减号？小数点分隔符呢？在某些语言中，有逗号而不是点。
The coalesce function retrieves the first non-null value from the parameters. In the example:
合并函数从参数中检索第一个非空值。在示例中：
coalesce (amount, 0)
If amount is null, it will return 0.
如果金额为空，则返回 0。
Are you sure you are passing a null value, or a string with the characters 'NULL', because in this case you have to convert the 'NULL' to '0' with the replace function.
您确定要传递一个空值，还是一个包含字符“NULL”的字符串，因为在这种情况下，您必须使用替换函数将“NULL”转换为“0”。
replace (amount, 'NULL', '0')
