The error message “Conversion failed when converting the varchar value to data type int” occurs in SQL Server client (for example Management Studio) 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.
CREATE TABLE [dbo].[Test_ISNUMERIC] (
[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.
- Let’s insert two additional text values into the table and run the same query again. Here, we encounter an error.
- 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.
SELECT
[Value]
,CASE
WHEN ISNUMERIC([Value]) = 1 THEN [Value]
ELSE 0
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.
