The error message “Conversion failed when converting the varchar value to data type int” occurs in SQL Server when attempting to convert a value stored as the varchar (text) data type to an integer data type. This might not be an issue if the varchar contains numeric values, but problems arise when non-numeric text is mixed in.

If you are interested in understanding the issue in more depth, read on. SQL Server can convert a varchar (text) data type to a number if the content can be converted. However, it cannot convert text to a number, leading to the error. Let’s demonstrate with an example:

  • We create a table with a varchar data type column and insert text values 1,2,3,4,5 into it.

[Value] VARCHAR(255)

INSERT INTO [dbo].[Test_ISNUMERIC] ([Value])
VALUES ('1'),('2'),('3'),('4'),('5');

  • As described, we can perform a conversion to a number and apply an aggregation operation, such as SUM. This works fine because the table contains only values that can be converted to numbers, resulting in 15.

Sum of text values

  • Let’s insert two additional text values into the table and run the same query again. Here, we encounter an error.

Conversion failed when converting the varchar value to data type int

  • Of course, there is a solution. If we want to perform the sum only on numeric values, we can use the ISNUMERIC() function. This function tests the values and returns 1 if the value is a number and 0 if it’s not. Consequently, we can create a simple SQL query with a CASE WHEN statement, replacing non-numeric values with zeros.

WHEN ISNUMERIC([Value]) = 1 THEN [Value]
END                       AS [Test_to_Integer]
FROM [dbo].[Test_ISNUMERIC];


We can see that non-numeric values have been replaced with 0. The column Test_to_Integer can now be summed, and when wrapped in a SUM aggregation function, we achieve the result of 15.

ISNUMERIC, aggregation

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *