While developing data processes in SQL Server, under certain circumstances, you might get the error message: error converting varchar to numeric. This error is similar with the conversion error you might get when you are trying to convert a varchar to float, etc.
Read on to find out the reason for getting this error message and how you can easily resolve it within just a minute.
The Numeric Data Type in SQL Server
Prior to discuss how you can reproduce and resolve the issue, it is important that you first understand the numeric data type in SQL Server. As described in the relevant MS Docs article, the numeric data type has fixed precision and scale, and it has equivalent functionality with the decimal data type.
The numeric data type takes two arguments, that is precision and scale. The syntax is numeric(precision, scale).
Precision defines the maximum number of decimal digits (in both sides of the number) and its value range is between 1 and 38.
Scale, defines the number of decimal digit that will be stored to the right of the decimal point. Its value can range between 1 and the value specified for precision.
Here’s an example of a numeric data type value in SQL Server:
DECLARE @numValue NUMERIC(10,2); SET @numValue=123456.7890 SELECT @numValue as NumValue; GO
The number returned by the above T-SQL query is: 123456.7890
In the above example I specified as precision 10 and as scale 2.
So, even though I specified 123456.7890 as the numeric value, it was indirectly converted to a numeric(10,2) value and that’s why it returned the value 123456.79
Reproducing the Conversion Error
Great. Now, let’s reproduce the conversion error by trying to convert a “problematic” varchar value to numeric.
You can find this example below:
DECLARE @valueToConvert VARCHAR(50); SET @valueToConvert='1123,456.7890'; SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber; GO
When you execute the above T-SQL code, you will get the below exact error message:
Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.
How to Resolve the Conversion Error
As you might have observed in the above example, the @valueToConvert variable, besides the dot (.), it also contains a comma (,).
Therefore, at the time of its conversion to the numeric data type, the comma is considered an illegal character for the destination data type (numeric) and that’s why you get the error message.
In order to resolve the conversion error, you just need to remove the comma (,) from the varchar value that you want to convert to numeric.
Note: At this point, you also need to make sure that the varchar value to be converted, is the actual number you wish to convert to the numeric data type. Also, you need to make sure that you only use the decimal symbol, in this case the dot (.), and not any digit grouping symbols, etc.
So, if we remove the comma from the above example, we can see that the conversion is successful.
DECLARE @valueToConvert VARCHAR(50); SET @valueToConvert='1123456.7890'; SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber; GO
In general, when converting varchar values to numbers (i.e. decimal, numeric, etc.), you need to be careful in order for your varchar value, not contain any digit grouping symbols (i.e. a comma) or any other characters that do not have a meaning as a number.
Feel free to check our other relevant articles on SQL Server troubleshooting:
- Error converting data type varchar to float
- SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified
- There is no SQL Server Failover Cluster Available to Join
- There is insufficient system memory in resource pool ‘internal’ to run this query.
- … all SQL Server troubleshooting articles
Featured Database Productivity Tools
Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.
Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)