Conversion Failed When Converting the varchar Value to Data Type int | Solved

When encountering the error message “conversion failed when converting the varchar value to data type int,” it’s typically an indication that there’s an attempt to convert a string (varchar) value into an integer (int) type, but the conversion process has failed due to incompatible data types. 

This error often arises in SQL queries, especially when dealing with data types conversion operations like CAST or CONVERT. If you encounter similar issues, consider leveraging conditional statements like CASE and ISNUMERIC to handle them gracefully and ensure query robustness. 

How To Solve Conversion Failed When Converting the varchar Value to Data Type int?

While it’s difficult to settle on a solution without viewing the actual code, in this section, we’ll go through two test case scenarios. 

Case 1: Data Type Mismatch

Let’s consider a SQL query where you’re joining tables and attempting to convert varchar values to integers.

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

The above query aims to retrieve data from multiple tables and perform conversions, but it encounters a conversion error due to non-numeric values in the a.value column.

Here, the error “Conversion failed when converting the varchar value ‘simple’ to data type int” arises due to an attempt to convert a varchar value to an integer. 

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

This line attempts to convert the varchar value a.value to an integer. However, it seems that some values in the a.value column are not purely numeric, leading to a conversion failure.

Solution

To handle scenarios where conversion to an integer might fail, we can use a combination of CASE and ISNUMERIC functions. Here’s how to adjust the code:

CONVERT(INT,
        CASE
            WHEN ISNUMERIC(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12), a.value)
            ELSE 0
        END)

By incorporating this CASE statement, we first check if the value can be converted to a numeric format. If it can, we perform the conversion; otherwise, we assign a default value (in this case, 0).

Why It Works

Using the CASE statement with ISNUMERIC allows us to conditionally handle values that cannot be converted to integers. By providing a default value (0 in this case), we prevent the conversion failure and ensure smooth execution of the query.

Case 2: Improper Conversion

In this example, the error occurs due to an attempt to concatenate a string literal with an integer parameter.

SET @CUSTOMERIDS= 'AND VU_CRM_Customers.CustomerID='+@CustomerID

In this line, single quotes encapsulate the string ‘AND VU_CRM_Customers.CustomerID=’, indicating it’s a string literal. However, @CustomerID is an integer parameter. When SQL Server attempts to concatenate these elements, it treats @CustomerID as a string within the string literal, leading to a failed conversion when the query expects an integer.

Solution

The solution is straightforward: use the CAST function to convert properly.

SET @CUSTOMERIDS = 'AND VU_CRM_Customers.CustomerID=' + CAST(@CustomerID AS VARCHAR(30))

By casting @CustomerID to VARCHAR(30), we ensure that it’s treated as an integer value and concatenated correctly with the string literal.

Why It Works

By removing the quotes, we prevent SQL Server from treating @CustomerID as a string within the string literal. Instead, it’s treated as an integer value, allowing for successful concatenation without triggering a conversion failure.

Frequently Asked Questions

What is the varchar data type in SQL?

Varchar stands for variable character, indicating that it’s a data type for character data with varying lengths. It’s a flexible string data type capable of storing numbers, letters, and special characters, and its length can vary based on the data it holds.

Can we convert varchar to int in SQL Server?

Yes, you can convert varchar to int in SQL Server using appropriate conversion functions like CAST or CONVERT. However, it’s crucial to ensure that the varchar value you’re converting contains only numeric characters to avoid conversion errors. 

Conclusion

In conclusion, you may need to handle scenarios where the varchar value may not be convertible to an integer, such as when it contains non-numeric characters or exceeds the integer range. By incorporating conditional logic, we can gracefully handle scenarios where conversion to a data type may not be straightforward.