Why does SQL Server 2005 accidentally truncate spaces?
Next 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, even though the criteria means it should return nothing. Also, it returns the length of two strings of whitespace, one of which is indeed 0, and also a length of 1, reporting as length 0.
Does anyone know what's going on here ?!
Refresh . It looks like it has something to do with the ANSI_PADDING database option covered by Microsoft in this KB article .
source to share
According to the documentation:
Returns the number of characters of the specified string expression, excluding trailing spaces.
There is a similar function called DataLength that you should familiarize yourself 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
Intermediate spaces are also removed for comparison purposes. You can accommodate this by converting the data to varbinary and comparing that way. If you do, I would leave the original comparison alone (for outside 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
source to share