SQL 将 varchar 值“simple,”转换为数据类型 int 时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21984110/
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
Conversion failed when converting the varchar value 'simple, ' to data type int
提问by ChangeTheWay
I am struggling for a few days with this issue and I can't figure out how can I fix it.
我在这个问题上挣扎了几天，我不知道如何解决它。
I would like to group bymy table on values 1,2,3,4,5so I have created a temporary tablewith this values.
我想要group by我的值表1，2, 3, 4，5所以我用这个值创建了一个临时表。
Now I have to INNER JOINthis table with other tables on a.value = #myTempTable.num.
现在我必须将INNER JOIN这张桌子与其他桌子放在一起a.value = #myTempTable.num。
BUT a.valueis ntextso I need to CONVERTit what I actually did, but I am getting an error:
但是a.value，ntext所以我需要按照CONVERT我实际做的来做，但是我收到了一个错误：
Conversion failed when converting the varchar value 'simple, ' to data type int. (on line 7)
将 varchar 值“simple,”转换为数据类型 int 时转换失败。（第 7 行）
Create table #myTempTable
(
num int
)
insert into #myTempTable (num) values (1),(2),(3),(4),(5)
SELECT a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) AS value, COUNT(*) AS pocet
FROM
(SELECT item.name, value.value
FROM mdl_feedback AS feedback
INNER JOIN mdl_feedback_item AS item
ON feedback.id = item.feedback
INNER JOIN mdl_feedback_value AS value
ON item.id = value.item
WHERE item.typ = 'multichoicerated' AND item.feedback IN (43)
) AS a
INNER JOIN #myTempTable
on CONVERT(INT, CONVERT(VARCHAR(12), a.value)) = #myTempTable.num
GROUP BY a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) ORDER BY a.name
drop table #myTempTable
I am not getting this error without the last INNER JOIN
如果没有最后一个，我不会收到此错误 INNER JOIN
INNER JOIN #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))
= #myTempTable.num
Could you help me please? I am desperate.
请问你能帮帮我吗？生无可恋。
Thanks.
谢谢。
回答by Milen
In order to avoid such error you could use CASE+ ISNUMERICto handle scenarios when you cannot convert to int.
Change
为了避免此类错误，您可以使用CASE+ISNUMERIC来处理无法转换为 int 的情况。
改变
CONVERT(INT, CONVERT(VARCHAR(12), a.value))
To
到
CONVERT(INT,
CASE
WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)
ELSE 0 END)
Basically this is saying if you cannot convert me to int assign value of 0 (in my example)
基本上这是说如果您不能将我转换为 int 分配值 0（在我的示例中）
Alternatively you can look at this article about creating a custom function that will check if a.valueis number: http://www.tek-tips.com/faqs.cfm?fid=6423
或者，您可以查看有关创建自定义函数以检查是否a.value为数字的这篇文章：http: //www.tek-tips.com/faqs.cfm?fid=6423
回答by Damien_The_Unbeliever
Given that you're only converting to ints to then perform a comparison, I'd just switch the table definition around to using varcharalso:
鉴于您只是转换为ints 然后执行比较，我只是将表定义切换为使用varchar：
Create table #myTempTable
(
num varchar(12)
)
insert into #myTempTable (num) values (1),(2),(3),(4),(5)
and remove all of the attempted CONVERTs from the rest of the query.
并CONVERT从查询的其余部分中删除所有尝试的s。
SELECT a.name, a.value AS value, COUNT(*) AS pocet
FROM
(SELECT item.name, value.value
FROM mdl_feedback AS feedback
INNER JOIN mdl_feedback_item AS item
ON feedback.id = item.feedback
INNER JOIN mdl_feedback_value AS value
ON item.id = value.item
WHERE item.typ = 'multichoicerated' AND item.feedback IN (43)
) AS a
INNER JOIN #myTempTable
on a.value = #myTempTable.num
GROUP BY a.name, a.value ORDER BY a.name
回答by Arima
If you are converting a varchar to int make sure you do not have decimal places.
如果要将 varchar 转换为 int，请确保没有小数位。
For example, if you are converting a varchar field with value (12345.0) to an integer then you get this conversion error. In my case I had all my fields with .0 as ending so I used the following statement to globally fix the problem.
例如，如果要将值为 (12345.0) 的 varchar 字段转换为整数，则会出现此转换错误。就我而言，我的所有字段都以 .0 结尾，因此我使用以下语句来全局解决问题。
CONVERT(int, replace(FIELD_NAME,'.0',''))
