将 nvarchar 转换为 int 的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3790591/
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
Sql query to convert nvarchar to int
提问by Simhadri
I have to query for total amount of a column using an aggregate function. The column data type is NVARCHAR(MAX). How can I convert it to Integer?
我必须使用聚合函数查询列的总量。列数据类型为 NVARCHAR(MAX)。如何将其转换为整数？
I have tried this:
我试过这个：
SELECT SUM(CAST(amount AS INT)),
branch
FROM tblproducts
WHERE id = 4
GROUP BY branch
...but I'm getting:
...但我得到：
Conversion failed when converting the nvarchar value '3600.00' to data type int.
将 nvarchar 值“3600.00”转换为数据类型 int 时转换失败。
回答by gbn
3600.00 is not integer so CAST via float first
3600.00 不是整数，所以首先通过浮点数进行 CAST
sum(CAST(CAST(amount AS float) AS INT))
Edit:
编辑：
Why float?
为什么要漂浮？
- no idea of precision or scale across all rows: float is the lesser evil perhaps
- empty string will cast to zero for float, fails on decimal
- float accepts stuff like 5E-02, fails on decimal
- 不知道所有行的精度或规模：浮动可能是较小的邪恶
- 空字符串将为浮点数转换为零，十进制失败
- float 接受像 5E-02 这样的东西，但在十进制上失败
回答by A-K
In addition to gbn's answer, you need to protect against non-numeric cases:
除了 gbn 的回答之外，您还需要防止出现非数字情况：
sum(CASE WHEN ISNUMERIC(Amount)=1 THEN CAST(CAST(amount AS float) AS INT)END )
回答by Adin Ballew
SELECT sum(Try_Parse(amount as Int Using 'en-US')),
branch
FROM tblproducts
WHERE id = 4
GROUP BY branch
SELECT sum(Try_Parse(amount as Int Using 'en-US')), 分支 FROM tblproducts
WHERE id = 4 GROUP BY 分支
