当将varchar值“simple”转换为数据类型int时，转换失败

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 by my table on values 1,2,3,4,5 so I have created a temporary table with this values.

我想按表对值1、2、3、4、5进行分组，因此我创建了一个具有这些值的临时表。

Now I have to INNER JOIN this table with other tables on a.value = #myTempTable.num.

现在我必须将这个表与a上的其他表进行内部连接。= # myTempTable.num价值。

BUT a.value is ntext so I need to CONVERT it what I actually did, but I am getting an error:

但一个。value是ntext，所以我需要把它转换成我实际做的，但是我得到了一个错误:

Conversion failed when converting the varchar value 'simple, ' to data type int. (on line 7)

当将varchar值“simple”转换为数据类型int时，转换失败。

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 #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))
= #myTempTable.num

Could you help me please? I am desperate.

你能帮我一下吗?我是绝望的。

Thanks.

谢谢。

3 个解决方案

#1

In order to avoid such error you could use CASE + ISNUMERIC to handle scenarios when you cannot convert to int.
Change

为了避免这种错误，您可以使用CASE + ISNUMERIC处理不能转换为int. Change时的场景

CONVERT(INT, CONVERT(VARCHAR(12), a.value))

来

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.value is number: http://www.tek-tips.com/faqs.cfm?fid=6423

或者，您也可以查看这篇关于创建自定义函数的文章，该函数将检查a。价值是:http://www.tek-tips.com/faqs.cfm?fid=6423

#2

Given that you're only converting to ints to then perform a comparison, I'd just switch the table definition around to using varchar also:

考虑到您只是将ints转换为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.

并从查询的其余部分中删除所有尝试的转换。

 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

#3

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',''))

#1