为什么SqlServer select语句会选择匹配的行和匹配的行并具有尾随空格的行 [英] Why would SqlServer select statement select rows which match and rows which match and have trailing spaces

问题描述

我创建了一个表，其中包含:

I have a table created with:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [nvarchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

我以此插入数据

insert into TestFeature1 (id,leng) values ('1',100);
insert into TestFeature1 (id,leng) values ('1 ',1000);

当我从表中选择以下内容时:

When I select from the table with this:

select * from TestFeature1 where id='1';

这将返回2行

1   100
1   1000

为什么会这样?为什么即使查询指定值仅是1，但没有空格，也为什么返回最后带有空格的版本?

why would this be? why is it returning the version with the space on the end even when the query specifies that the value is only a 1 on its own, with no space?

推荐答案

有趣的是，如果您使用LIKE，则可以使用:

Interestingly, works if you use LIKE:

select * from TestFeature1 where id LIKE '1'

编辑:在进行了更多研究之后，我发现其他人与我们进行了相同的对话.请参见此处.该评论仅在讨论中进行了一半.但是结果是如我们所发现的，要么如上所述使用LIKE，要么添加第二个条件以检查列的DATALENGTH和提供的值是否相同.我更喜欢LIKE路线.

after a bit more research I found others have had the same conversation as us. See here. That particular comment is half way through the discussion. But the outcome was as we have found, either use LIKE as demonstrated above, or add a 2nd condition to check the DATALENGTH of the column and supplied value are the same. I prefer the LIKE route.

这篇关于为什么SqlServer select语句会选择匹配的行和匹配的行并具有尾随空格的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！