Conversion failed when converting the varchar value 'simple, ' to data type int

group-by, sql, sql-server, t-sql

Solution

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

Problem

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 someone help me please? Thanks.

Original source