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

Question

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

User · Accepted Answer

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

User · Answer

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   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

User · Answer

If you are converting a varchar to int make sure you do not have decimal places   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   CONVERT int  replace FIELD NAME   0

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to group-by