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

Thanks.

Even if your `WHERE` clause should, logically, prevent it attempting the conversion of any non-numeric strings, unfortunately that's not the reality. See [SQL Server should not raise illogical errors](https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors) on Connect. — Damien_The_Unbeliever, Feb 24 '14 at 10:13
Thanks for advise, but I am sure that _value_ contains only numeric values (1-5) — ChangeTheWay, Feb 24 '14 at 10:52
if the `value` column *only* contains numeric values 1-5, why is it not declared using a numeric type, e.g. `tinyint`? — Damien_The_Unbeliever, Feb 24 '14 at 10:55
*It doesn't contains only numeric values, but with condition `item.typ = 'multichoicerated'` I am selecting only numeric values — ChangeTheWay, Feb 24 '14 at 11:00
And that is **exactly** what I was trying to warn you about - re-read my first comment. You're relying on "the `WHERE` clause should, logically, prevent ...". The optimizer has decided to perform the conversion *before* it considers the `WHERE` clause. — Damien_The_Unbeliever, Feb 24 '14 at 11:01
Out of curiosity, where was the value "simple, " coming from? — WEFX, Oct 02 '14 at 15:00

score 77 · Accepted Answer · edited May 29 '18 at 12:11

77

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

edited May 29 '18 at 12:11

Oreo

529
3
16

answered Feb 24 '14 at 10:20

Milen

8,697
7
43
57

1

`ISNUMERIC('£')` produces 1, and yet you cannot convert `£` to an `int`. – Damien_The_Unbeliever Feb 24 '14 at 10:22
Thanks for response. But what if I can say I am 100% sure that all values are int? P.S. I have tried your solution but I get an error _Conversion failed when converting the varchar value '.' to data type int._ – ChangeTheWay Feb 24 '14 at 10:47
@user1535786 - that's just yet another example of why `ISNUMERIC` isn't the correct tool. It answers a question no-one has ever asked - "Can I convert this string to *any* of the numeric data types? I don't care *which*, if any, of the types it can be converted to, and there's no need to tell me which types it works for" – Damien_The_Unbeliever Feb 24 '14 at 10:58
2

Thanks for help guys! I used a function from the link and now it finally works. – ChangeTheWay Feb 24 '14 at 11:26
Thank you! Mentioned function working for me and solved my problem – Muhammad Saad Jan 03 '23 at 11:50

score 7 · Answer 2 · edited Jan 05 '18 at 20:39

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

score 5 · Answer 3 · answered Feb 24 '14 at 11:26

5

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

answered Feb 24 '14 at 11:26

Damien_The_Unbeliever

234,701
27
340
448

I have tried similar solution like this but I was not succesful. In this case it says _The data types ntext and varchar are incompatible in the equal to operator._ after CONVERT ntext to varchar I was facing some other issues. – ChangeTheWay Feb 24 '14 at 11:29
@user1535786 - well, it's time to move away from `ntext` as well - the type is deprecated in any version of SQL Server that's still supported. – Damien_The_Unbeliever Feb 24 '14 at 11:35
totally agree with you but I can't even change it, I would have to recoding huge part of the application – ChangeTheWay Feb 24 '14 at 12:36

score 0 · Answer 4 · answered Apr 21 '23 at 13:42

For anyone who gets this error message when performing a UNION ALL, remember that the column order and type must be the same for each table. If you try to union an INT column with a VARCHAR you would get this error.

Not the cause in the case of this particular question, but I'm adding it since this is the top search result for this error message.

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

4 Answers4

Linked