为什么SQL Server 2005会随机截断空白？

The following T/SQL:-

以下T / SQL: -

create table #temp(foo varchar(10))

insert into #temp select ''
insert into #temp select 'abc'
insert into #temp select ' '

select len(foo) from #temp where foo = '  '

drop table #temp

Returns two 0's, despite the fact the criteria means it should return nothing. Additionally it's returning the length of the two whitespace rows, one of which is indeed of length 0 but also one which is of length 1, reporting both as length 0.

返回两个0,尽管标准意味着它应该什么都不返回。此外,它返回两个空白行的长度,其中一个确实长度为0,但也是一个长度为1的行,报告长度为0。

Does anybody know what's going on here?!

有谁知道这里发生了什么?!

Update: It appears this is related to the ANSI_PADDING database option covered by Microsoft in this KB article.

更新:在本知识库文章中,这似乎与Microsoft所涵盖的ANSI_PADDING数据库选项有关。

1 个解决方案

#1

According to the documentation:

根据文件:

Returns the number of characters of the specified string expression, excluding trailing blanks.

返回指定字符串表达式的字符数,不包括尾随空格。

There is a similar function named DataLength that you should become familiar with.

你应该熟悉一个名为DataLength的类似函数。

create table #temp(foo varchar(10))

insert into #temp select ''
insert into #temp select 'abc'
insert into #temp select ' '

select len(foo), DataLength(foo) from #temp where foo = '  '

drop table #temp

Trailing spaces are removed for comparison purposes, too. You can accommodate this by converting the data to varbinary and comparing it that way. If you do this, I would leave the original comparison alone (for sargability reasons).

删除尾随空格也是为了进行比较。您可以通过将数据转换为varbinary并以此方式进行比较来实现此目的。如果你这样做,我会单独留下原始比较(出于可怜的原因)。

create table #temp(foo varchar(10))

insert into #temp select ''
insert into #temp select 'abc'
insert into #temp select ' '

select len(foo), DataLength(foo) 
from    #temp 
where   foo = ' '
        and Convert(varbinary(8), foo) = Convert(VarBinary(8), ' ')

drop table #temp

#1