Ошибка при преобразовании типа данных varchar к numeric

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.

Arguments

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

---

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $12.99

---

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

Output:

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.

Check our Online Courses

• SQL Server 2022: What’s New – New and Enhanced Features
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• Error converting data type varchar to float
• Rule “Setup account privileges” failed – How to Resolve
• SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
• 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.
• There is not enough space on the disk. (mscorlib)
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• Introduction to Azure Database for MySQL (Course Preview)
• [Resolved] Operand type clash: int is incompatible with uniqueidentifier
• The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
• SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it
• Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
• An existing History Table cannot be specified with LEDGER=ON – How to Resolve
• Advanced SQL Server Features and Techniques for Experienced DBAs
• SQL Server Database Backup and Recovery Guide
• … 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.

Learn more

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

Rate this article: (3 votes, average: 4.33 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

How to resolve the error: Error converting varchar to numeric in SQL Server

Click to Tweet

Views: 24,359

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog’s Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can’t address them now.

P.S. I had a brainstorm today. An alternate to the «traditional way» that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I’ve noticed that since learning CROSS/OUTER APPLY I’ve improved my query capability with older SQL Server versions, too—as I am getting more versatile with the «outer reference» capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It’s a lot shorter than the CASE statement.

If you’re getting SQL Server error Msg 8114 that reads something like Error converting data type varchar to numeric, it’s probably because you’re trying to perform a data type conversion that fails due to the value not being able to be converted to the destination type.

It’s not because you can’t convert that type to the new type. It’s because of the value itself.

Example of Error

Here’s an example of code that produces the error:

SELECT CAST('Ten' AS DECIMAL(5,2));

Result:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This error occurred because SQL Server couldn’t convert the string value to decimal.

Solution 1

To fix this, you’d need to make sure you provide a value that SQL Server can convert.

If you’re passing a column, check that you’ve got the right column. Same if you’re passing a variable – check that it’s the right variable.

Bear in mind that this might not happen in all cases of trying to convert a string to decimal, because some string values can be converted.

For example, the following conversion succeeds:

SELECT CAST('10' AS DECIMAL(5,2));

Result:

10.00

Here, SQL Server was able to work out that 10 is a number, and therefore the conversion succeeded.

Solution 2

If you don’t mind the conversion failing, but you just don’t want it to return an error, try the TRY_CAST() or TRY_CONVERT() functions.

Rather than return an error, these functions return NULL when the value can’t be converted.

Example:

SELECT TRY_CAST('Ten' AS DECIMAL(5,2));

Result:

NULL