Mssql error converting data type varchar to 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 $14.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 [New]
• 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
• 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
• … 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: 18,900

Mssql error converting data type varchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’m trying to convert a field of datatype varchar to numeric and its failing with the following error

Error converting data type varchar to numeric. Please see the sql am using and the sample data.Please need help!

Answers

Thanks Visakh16, from the link i created a function and executed the sql. Please see the below query and result set. Its showing validty 0 for ‘p’ and ‘NULL’. I can ask the users to correct the «P» record, but «NULL» are valid i cant filter them. What is the workaround for that?

Nope thats not true

NULL represents an unknown value so you wont be able to determine a data type for it

Hence validation functions like ISNUMERIC,ISDATE etc should always return false for it

So the function is working as per design

if you want to include them too you need to write condition as this

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
—————————-
My MSDN Page
My Personal Blog
My Facebook Page

case when value NOT LIKE ‘%[^0-9]%’ THEN CAST ( value as numeric ( 6 , 2 )) ELSE NULL END

The values contain ‘.’ which is not 0-9. So no, it should not solve your problem, unless the values are not decimal.

There isn’t anything fundamentally wrong with what you did:

Both of these queries run just fine.

Can you provide the full DDL, DML and any attached objects (triggers et al)? There maybe some clues there.

Yup, it’s an invalid SQL SELECT statement. E.g. as a stand-alone example

This should run in your environment:

Thanks Patrick, below is the complete query

SV, scientific notation would cause this issue. For instance, ‘3.12332E10’ would succeed the test of the isnumeric function but would fail to convert. It happens almost everytime I import from excel.

Look for values containing ‘E’. If it is not your case then I would suggest doing a dichotonic search to figure out the failing value (split the result in half until you find the error).

To illustrate what I just said

Without example data from all of those objects, we won’t be able to test the full query for you. If you are sure the problem is on the original line you posted, we’d need to see any other objects associated with that column. Are there any triggers on the table?

Also, try aliasing your tables, to make referencing them quicker and easier (and improving the readability of your code). Aliasing works like this:

SV, I believe I had this issue with scientific notation. For instance, ‘3.12332E10’ would succeed the test of the isnumeric function but would fail to convert.

Look for values containing ‘E’. If it is not your case then I would suggest doing a dichotonic search to figure out the failing value (split the result in half until you find the error).

Actually, numbers even as large as 1234567890123456789012345678.00 convert to numeric from varchar, without an issue:

Since the original convert was only to 6, if any number larger than 999999.99 was encountered, it would have generated an overflow error.

Like you observed i have maany calculations in the sql. I zeroed on this particular «total_dollar_amount» calculation by commenting it out and re running the query and it worked fine. The problem is with this conversion only.There are no other objects that are associated with column and NO there are no triggers either.

No, negative values won’t cause a problem.

However, currency signs can. If you’re using SQL Server 2012 or newer, you can use TRY_CONVERT:

This should return all rows which cannot be converted.

SV, I believe I had this issue with scientific notation. For instance, ‘3.12332E10’ would succeed the test of the isnumeric function but would fail to convert.

Look for values containing ‘E’. If it is not your case then I would suggest doing a dichotonic search to figure out the failing value (split the result in half until you find the error).

Actually, numbers even as large as 1234567890123456789012345678.00 convert to numeric from varchar, without an issue:

Since the original convert was only to 6, if any number larger than 999999.99 was encountered, it would have generated an overflow error.

Источник

Mssql error converting data type varchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I am getting following error when I am trying to insert data into target table.
«Error converting data type varchar to numeric.»

so I tried to debug by inserting few number of rows to find out if there is a problem in data. But when I tried to divide data and insert the data, it is inserting and not giving any error. But when I try to insert large volume of data it is giving above error.

Please needed urgently

Answers

We don’t know much about your INSERT statement, but from what you say, it sounds like this is a «false» error. That is, it is an error that occurs with data that is supposed to be filtered out by the WHERE clause in your SELECT statement, but because SQL Server recasts computation order from the logical order, the error occurs. However, when you try to divide and conquer, the optimizer outsmarts you by choosing a different plan which does not have this problem.

As Tom says, TRY_CAST/TRY_CONVERT is the easiest way out.

Regards, Muthukumar Balu

i think your trying to insert records from varchar or char column to INT column

when you check the fewer column may be that data set may only contain the data that can the implicit convert to int

a good may be to find the incorrect data and fix it, below query may help you to start with

Thanks Saravana Kumar C

You will get much better help if you post the INSERT statement that is getting this error. If you are on SQL 2012 or later, I would recommend you use either the TRY_CAST or TRY_CONVERT function to change the data from varchar to numeric.

We don’t know much about your INSERT statement, but from what you say, it sounds like this is a «false» error. That is, it is an error that occurs with data that is supposed to be filtered out by the WHERE clause in your SELECT statement, but because SQL Server recasts computation order from the logical order, the error occurs. However, when you try to divide and conquer, the optimizer outsmarts you by choosing a different plan which does not have this problem.

As Tom says, TRY_CAST/TRY_CONVERT is the easiest way out.

I am getting following error when I am trying to insert data into target table.
«Error converting data type varchar to numeric.»

so I tried to debug by inserting few number of rows to find out if there is a problem in data. But when I tried to divide data and insert the data, it is inserting and not giving any error. But when I try to insert large volume of data it is giving above error.

It may be that the offending data is coming in later batches so initial batches might have loaded fine

The main places you need to focus are the cases where you try to populate decimal,numeric data from character based source. Make sure the source field does not have any characters which makes it an invalid numeric data. If you’re doing an implicit conversion make sure you tidy the data out to valid numeric format before you apply cast or convert or it .In case you’re on 2012 version you can apply TRY_CAST or TRY_CONVERT which will not raise it as an error but just returns a NULL in case of conversion error.

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
—————————-
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Источник

Mssql error converting data type varchar to numeric

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have created a stored procedure where I am collecting data against input phone number.

STORED PROCEDURE:

USE [CCCust_StoreData]
GO
/****** Object: StoredProcedure [dbo].[sp_StoreData] Script Date: 12/29/2014 08:44:14 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

EXEC [sp_StoreData] ‘01217645142’

ALTER PROCEDURE [dbo].[sp_StoreData](@DNIS_NO varchar(255)) AS

/*
This procedure returns key value pairs containg the store info based on a lookup in the STOREDATA_CUSTPLANT database tables
*/

DECLARE @recCnt int,
@matchrec int,
@intTelNo NUMERIC(21,0),
@retString varchar(800)

IF isNumeric(@DNIS_NO) != 1
BEGIN
SELECT ‘STORE_NUMBER:|STORE_NAME:|PLANT:’
RETURN
END

SET @recCnt = 0
SET @intTelNo = CAST(@DNIS_NO AS NUMERIC(21,0))

SELECT @recCnt = COUNT(DISTINCT MP.STORE_NUMBER)
FROM STOREDATA_CUSTPLANT MP
WHERE CAST(‘0’+MP.PHONE_NUMBER AS NUMERIC(21,0)) = @intTelNo

IF @recCnt > 0
BEGIN
— If only one record matches — return the info
SELECT TOP 1 ‘STORE_NUMBER:’ + CAST(@recCnt AS VARCHAR) + ‘|STORE_NAME:’ + ISNULL(MP.STORE_NAME, ») + ‘|PLANT:’ + ISNULL(MP.PLANT, »)
FROM [STOREDATA_CUSTPLANT] MP

WHERE CAST(‘0’+MP.PHONE_NUMBER AS NUMERIC(21,0)) = @intTelNo

RETURN
END
ELSE
BEGIN
SELECT ‘STORE_NUMBER:|STORE_NAME:|CONTACT_ID:|PLANT:’
RETURN
END

Data Types:

STORE_NUMBER data type is set on table is varchar(50)
STORE_NAME data type is set on table is varchar(50)
PLANT data type is set on table is varchar(50)
PHONE_NUMBER data type is set on table is varchar(50)

When I try to execute sp_StoreData, I get the following error:

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

Can someone please help me.

Answers

Stored Procedure is OK. However, table did contain some wrong record which cause error in handling the query.

• Marked as answer by Lydia Zhang Microsoft contingent staff Thursday, January 1, 2015 7:35 AM

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
—————————-
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

• Edited by Visakh16 MVP Monday, December 29, 2014 1:30 PM
• Proposed as answer by Tom Phillips Monday, December 29, 2014 2:43 PM

Balmukund Lakhani
Please mark solved if I’ve answered your question, vote for it as helpful to help other users find a solution quicker
———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.
———————————————————————————
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn — Paperback, Kindle

Yes, you are right.

Table have following data:

STORE_NUMBER	STORE_NAME	PLANT	PHONE_NUMBER
50002430	ALDI ACOCKS GREEN (12)	UU19	01217645142
50011339	ALDI ALFRETON (62)	UU19	01773540874
50007706	ALDI ASHBY DE LA ZOUCH (91)	UU19	01530560629

due to round brackets in STORE_NAME, error occurs.

Can you please tell me how I can solve this issue ? what data type I can set to solve this.

This should reveal the columns with bad data.

Don’t name your procedure sp_ as sp_ prefix is reserved for MS (system) stored procedures. Any other prefix will be OK, just not ‘sp_’

Also, ISNUMERIC doesn’t filter non-numeric items as it’s a wide spec function. If you’re using SQL 2012 and up, I suggest to use TRY_CAST function instead.

Check this blog post

For every expert, there is an equal and opposite expert. — Becker’s Law

>> I have created a stored procedure where I am collecting data against input phone number. and not the C family !=. Why did you use the magical VARCHAR(50) default for everything? Old ACCESS programmers make that mistake. We also gave up camelCase years ago; it does not work and will increase the time to maintain code.

But the error that makes RDBMS people laugh is a numeric telephone number. Are you going to take a square root of a phone number? And it can be FIFTY characters long?

This is not how to use SQL or RDBMS. If this were a carpentry forum, you would be the guy who is trying to saw wood with a screwdriver.

Read the E.123 Standards for phone numbers and write a CHECK() constraint on a column based on it. I find it is better to get a data scrubbing tool and let it format and validate phone numbers instead of wasting time to write my own.

—CELKO— Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Источник

SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

The Arithmetic Overflow Error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

Example Query :