Why is SQL Server 2005 Randomly Truncating Whitespace?

Question

The following 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.

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.

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

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

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