Ms sql 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!

(Lifetime Access/ Live Demos / Downloadable Resources and more!)
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

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: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (3 votes, average: 4.33 out of 5)

Loading…

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

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

Click to Tweet

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Источник

Remove From My Forums

Question
Good Afternoon,

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!
```
SELECT CASE 
       Isnumeric( 
      accela_staging.dbo.fact_highweedsandgrassdetails.vendorinvoiceamount) 
      WHEN 1 THEN Convert(numeric(6,2), dbo.fact_highweedsandgrassdetails.vendorinvoiceamount)
         --CAST(fact_highweedsandgrassdetails.vendorinvoiceamount AS NUMERIC(9,2)) 
     END                                                        AS 
       Total_Dollar_Amount
```
```
VendorInvoiceAmount
p
97.50
84.00
65.00
55.00
420.00
350.00
325.00
325.00
240.00
185.00
182.00
175.00
150.00
150.00
140.00
140.00
135.00
130.00
130.00
125.00
120.00
115.00
104.00
100.00
10.00
0.00
-65.00
-65.00
-55.00
-55.00
-50.00
0.00
NULL
```
SV

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?
```
select distinct k.VendorInvoiceAmount,k.valid
from
(select CaseNumber,VendorInvoiceAmount, dbo.is_numeric(VendorInvoiceAmount)as valid 
from dbo.FACT_HighWeedsAndGrassDetails
)k
```
```
VendorInvoiceAmount	valid
125.00	1
240	1
185.00	1
-55	1
130.00	1
104.00	1
0	1
.	1
100	1
150.00	1
p	0
55	1
140	1
130	1
140.00	1
-65	1
182.00	1
-55.00	1
NULL	0
65	1
84.00	1
100.00	1
325	1
175.00	1
-0	1
10	1
350	1
97.50	1
-50	1
150	1
120	1
135.00	1
115	1
420.00	1
-65.00	1
325.00	1
```
SV
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

see this
```
SELECT ISNUMERIC(NULL),ISDATE(NULL)
```
So the function is working as per design

if you want to include them too you need to write condition as this
```
select distinct k.VendorInvoiceAmount,k.valid
from
(select CaseNumber,VendorInvoiceAmount, dbo.is_numeric(VendorInvoiceAmount)as valid 
from dbo.FACT_HighWeedsAndGrassDetails
)k
WHERE valid = 1
OR VendorInvoiceAmount IS NULL
```
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
- Marked as answer by
  
  Tuesday, October 28, 2014 3:06 PM
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.
- Marked as answer by
  saivenkat77
  Tuesday, October 28, 2014 3:05 PM

Источник

Remove From My Forums

Question
DECLARE @ENTITY nvarchar (100)

set @ENTITY = ‘AccidentDimension’

DECLARE @FIELD nvarchar (100)

set @FIELD = ‘JurisdictionState’

DECLARE @KEYID nvarchar (100)

SET @KEYID = ‘1234567890’

DECLARE @VALUE nvarchar (100)

SET @VALUE = ‘WI’

DECLARE @WC_TABLE NVARCHAR(100)

SET @WC_TABLE = ‘WorkingCopyAdd’ + @ENTITY

DECLARE @SQL1 NVARCHAR (1000)

SET @SQL1 = ‘INSERT INTO ‘ + @WC_TABLE+ ‘ (Claim, ‘+ @Field +‘) VALUES (»’+ @KEYID +»’, »’+@VALUE+»’)’

EXECUTE sp_executesql @SQL1

Can somebody help me. I get this error:

Error converting data type varchar to numeric.

while executing this Dynamic TSQl Command

Answers

You are in wrong direction, The default won’t help you here..

The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.

In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…

As per the BOL,

Column definition	No entry, no DEFAULT definition	No entry, DEFAULT definition	Enter a null value
Allows null values	NULL	Default value	NULL
Disallows null values	Error	Default value	Error

So, you have to use the ISNULL function to fix your problem.

Code Snippet

Create table #Staging1

(

Id int,

Name varchar(10)

)

Insert Into #Staging1 Values(1, NULL);

Insert Into #Staging1 Values(1, ‘test’);

Create table #Main

(

ID int NOT NULL,

Name varchar(10) NOT NULL DEFAULT (»)

);

—Will Work Fine

Insert Into #Main(ID)

Select ID From #Staging1

—Should Fail

Insert Into #Main(ID,Name)

Select ID,Name From #Staging1

—Will Work

Insert Into #Main(ID,Name)

Select ID,Isnull(Name,») From #Staging1

Источник

Содержание

Mssql error converting data type varchar to numeric
Answered by:
Question
Answers
All replies
Mssql error converting data type varchar to numeric
Answered by:
Question
Answers
All replies
Mssql error converting data type varchar to numeric
Answered by:
Question
Answers
All replies

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.

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.»

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

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

Источник

The Numeric Data Type in SQL Server

Arguments

Reproducing the Conversion Error

How to Resolve the Conversion Error

Check our Online Courses

Read Also

Featured Database Productivity Tools

Question

Answers

Question

Answers

Mssql error converting data type varchar to numeric

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers

Mssql error converting data type varchar to numeric

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers

Mssql error converting data type varchar to numeric

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers