为什么SqlServer select语句会选择匹配的行和匹配并带有尾随空格的行

2021-12-19 00:00:00 sql select sql-server

我创建了一个表:

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.

相关文章