将 varchar 值“simple,"转换为数据类型 int 时转换失败

我为此问题苦苦挣扎了几天，但不知道如何解决.

我想group by我的表格1,2,3,4,5 所以我用这个值创建了一个临时表.

现在我必须在 a.value = #myTempTable.num 上INNER JOIN 这个表和其他表.

但是 a.value 是 ntext 所以我需要 CONVERT 它我实际上做了什么，但我收到一个错误:<块引用>

将 varchar 值 'simple,' 转换为数据时转换失败输入整数.(在第 7 行)

创建表#myTempTable(整数)插入#myTempTable (num) 值 (1),(2),(3),(4),(5)SELECT a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) AS value, COUNT(*) AS pocet从(SELECT item.name, value.valueFROM mdl_feedback AS 反馈INNER JOIN mdl_feedback_item AS 项目ONfeedback.id = item.feedbackINNER JOIN mdl_feedback_value AS 值ON item.id = value.itemWHERE item.typ = 'multichoicerated' AND item.feedback IN (43)) 作为一个内连接#myTempTableon CONVERT(INT, CONVERT(VARCHAR(12), a.value)) = #myTempTable.numGROUP BY a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) ORDER BY a.name删除表#myTempTable

如果没有最后一个 INNER JOIN

，我不会收到此错误

INNER JOIN #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))= #myTempTable.num

你能帮我吗?我很绝望.

谢谢.

解决方案

为了避免此类错误，您可以使用 CASE + ISNUMERIC 来处理无法转换的场景到国际.
改变

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

到

CONVERT(INT,案件WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)其他 0 结束)

基本上这是说如果您不能将我转换为 int 分配值 0(在我的示例中)

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

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.

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

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

Conversion failed when converting the varchar value 'simple, ' to data type int. (on line 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 #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))
= #myTempTable.num

Could you help me please? I am desperate.

Thanks.

解决方案

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

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)

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