Error converting data type varchar to numeric ошибка - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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.

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 $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

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

Источник

SELECT ID,
CASE WHEN ISNUMERIC(COL_VALUE) = 1 THEN CONVERT(NUMERIC, COL_VALUE) * 1000
  ELSE COL_VALUE 
END AS [COL_VALUE]
FROM Table

The original data type is varchar that is why I convert COL_VALUE to numeric.
It seems like something wrong with ELSE statement, when I execute the query without ELSE statement the non-numeric value will become NULL. I check whether or not the column is numeric, if it is numeric then multiply by 1000, if not numeric then return original value. There are few non-numeric values like:

23`, 34/, 34=4.

Источник

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

Источник

SELECT ID,
CASE WHEN ISNUMERIC(COL_VALUE) = 1 THEN CONVERT(NUMERIC, COL_VALUE) * 1000
  ELSE COL_VALUE 
END AS [COL_VALUE]
FROM Table

23`, 34/, 34=4.

asked Jul 6, 2015 at 16:59

Since you are returning numeric and non umeric values you shoud cast everything back to varchar. Also use try_cast function because isnumeric function will return sometimes true when you have dollar sign in your string for example and convert function will fail:

SELECT ID,
       CASE WHEN TRY_CAST(COL_VALUE AS NUMERIC) IS NOT NULL 
            THEN CAST(CAST(COL_VALUE AS NUMERIC) * 1000 AS VARCHAR(100))
            ELSE COL_VALUE 
       END AS [COL_VALUE]
FROM Table

answered Jul 6, 2015 at 17:17

Giorgi NakeuriGiorgi Nakeuri

35k8 gold badges47 silver badges74 bronze badges

try this query

SELECT ID,
CASE WHEN ISNUMERIC(COL_VALUE) = 1 THEN Convert(varchar(50), CONVERT(NUMERIC, COL_VALUE) * 1000)
  ELSE COL_VALUE 
END AS [COL_VALUE]
FROM Table

answered Jul 6, 2015 at 17:19

Mukesh KalgudeMukesh Kalgude

4,8142 gold badges17 silver badges32 bronze badges

Источник

And?

So, you have gotten an error message. But there is no reason to panic. It happens to everyone. It’s something you will need to be able to handle to be able to handle to be successful in your career as a developer.

The first step is always to understand what the error comes from. This error says «Error converting data type varchar to numeric.» Thus, somewhere in the query you mix data types. Many languages are very strict and would give you a compile-time
error, but SQL Server is very forgiving at that point and supply implicit conversion between strings and numbers. That does not mean that every conversion will complete successfully. For instance how would the value ‘Rögle till Elitserien!’ convert to
a number?

Since I don’t know your tables, I cannot say for sure where you have your error. You on the other hand, has that piece of information at your fingertips, so you can easily investigate.

However, I do see a possibility:

SELECT RecTrNo, RecTrDate, DesName, » as Debit, RecStitch as Credit, TaName
…
Select PTrNo, PTrDate, PRemk, PAmnt, » as Credit, TaName

Columns that get the alias Debit and have a name ending in Amnt are typically not varchar but of some numeric data type, so my guess this is the same here.

I don’t know what you intended, but SQL Server applies a strict data type precedence, so when two data types meet, the one with lower precedence is converted to the type with higher precedence. If 1) there is an implicit conversion and 2) the value is convertible.
varchar ranks fairly low in this precedence, so SQL SQL attempts to convert the empty string to numeric, but if you try:

select convert(numeric, »)

You get the very same error message.

Just beacuse SQL Server permits you mix data types, does not mean that should do it or rely on it. Personally, I think this is a misfeature, and there would be far less confusion if there was no such implicit conversion.

You should probably use NULL instead. If you really want string values, you need to explicitly convert the numeric values to strings.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Marked as answer by

Friday, March 30, 2012 1:42 PM

Источник

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