当将varchar值'0%'转换为数据类型int时，转换失败

I'm facing a problem with SQL Server.

我在SQL Server上遇到了一个问题。

I've created a table like this:

我创建了这样一个表格:

create table Components 
(
    pk BIGINT NOT NULL PRIMARY KEY IDENTITY,
    id VARCHAR(50),
    descr VARCHAR(50),
    in_m INT,
    in_iy INT,
    p_fw VARCHAR(5000)
);

and I'm trying to insert a couple of values:

我想插入几个值

insert into Components (id, descr, in_m, in_iy, p_fw) 
values ('FW000_A', '0%', 0, 0, '[0.0,0.0,0.0]'),
       ('FW000_B', '1%', 1, 1, '[1.0,1.0,1.0]');

I get the following error:

我得到以下错误:

Msg 245, Level 16, State 1, Line 111
Conversion failed when converting the varchar value '0%' to data type int.

Msg 245，级别16，状态1，第111行转换失败时，将varchar值'0%转换为数据类型int。

even though the column descr is correctly defined as varchar(50).

即使列descr被正确定义为varchar(50)。

Can anybody help me please? Why is SQL Server trying to convert my strings to int values?

谁能帮帮我吗?为什么SQL Server试图将字符串转换为int值?

2 个解决方案

#1

What's missing from your question is that you have more than just the two values lines you've shown, and one of the other ones has an integer literal for the descr column, rather than a string.

您的问题中缺少的是，您不仅有两个值行，还有一个值行具有descr列的整型文字，而不是字符串。

This example produces the same error:

这个例子产生了相同的错误:

declare @t table (Descr varchar(50) not null)
insert into @t(Descr) values
('0%'),
(12)

What I believe happens is that SQL Server first tries to determine the data types for all columns in the values clause. Using data type precedence rules, it observes a varchar literal in one row and an int literal in the other, and so determines that the overall type for this column is int and attempts to perform the conversion that leads to the error.

我认为，SQL Server首先尝试为values子句中的所有列确定数据类型。使用数据类型优先规则，它在一行中观察varchar文字，在另一行观察int文字，因此确定该列的总体类型为int，并尝试执行导致错误的转换。

During this process, it does not use any information about the target table into which the values are going to be placed, including the data types of the columns there.

在此过程中，它不使用关于将要放置值的目标表的任何信息，包括那里的列的数据类型。

#2

run this and verify the data types;

运行它并验证数据类型;

  sp_columns Components

Looks like 'descr' is really an integer

看起来descr实际上是一个整数

#1