Sql server ignores trailing spaces while comparing using WHERE
and HAVING clauses .However this behavior is different for LIKE clause.
Lets some examples on it
create table #temp ( a varchar(10) )
insert into #temp values(' a ')
select COUNT(*) from #temp group by a having a=' a ' ->1
select COUNT(*) from #temp where a=' a ' ->1
select COUNT(*) from #temp where ' a '=a ->1
select COUNT(*) from #temp where a='a' ->0
select COUNT(*) from #temp where a like ' a ' ->1
select COUNT(*) from #temp where a like ' a' ->1
select COUNT(*) from #temp where a like ' a '->0
select COUNT(*) from #temp where ' a ' like a ->1
select COUNT(*) from #temp where ' a' like a->0
drop table #temp
For LIKE sql has following principle when the
string is ASCII(i.e. for varchar and char etc…)
Msdn:
|
When you perform string
comparisons by using LIKE, all characters in the pattern string are
significant. This includes leading or trailing spaces. If a comparison in a
query is to return all rows with a string LIKE 'abc ' (abc followed by a
single space), a row in which the value of that column is abc (abc without a
space) is not returned. However, trailing blanks, in the expression to which
the pattern is matched, are ignored. If a comparison in a query is to return
all rows with the string LIKE 'abc' (abc without a space), all rows that
start with abc and have zero or more trailing blanks are returned.
|
However when string is Unicode(nvarchar…) the above rule is not applicable. i.e. there is no
significance of trailing spaces .
Lets try above example on a Unicode string
create table #temp ( a nvarchar(10) )
insert into #temp values(' a ')
select COUNT(*) from #temp group by a having a=' a ' ->1
select COUNT(*) from #temp where a=' a ' ->1
select COUNT(*) from #temp where ' a '=a ->1
select COUNT(*) from #temp where a='a' ->0
select COUNT(*) from #temp where a like ' a ' ->1
select COUNT(*) from #temp where a like ' a' ->0
select COUNT(*) from #temp where ' a ' like a->0
select COUNT(*) from #temp where ' a' like a ->0
drop table #temp
So LIKE is used for pattern matching and not for comparisons.
And we need to be extra careful when using LIKE and might take care of trailing
spaces using RTRIM to be very sure of correct data retrieval.
For where clause trailing spaces are ignored as can be seen
in example and RTRIM can be avoided.
Now we need to remember that string storage is affected by ANSI_PADDING setting which is by default ON .But
the comparison is not affected.
Again as storage changes , pattern matching(LIKE) changes accordingly. This is
very important to see.
SET ANSI_PADDING OFF
create table #temp ( a varchar(10) )
insert into #temp values(' a ')
select DATALENGTH(a) from #temp ->2
select COUNT(*) from #temp where a=' a ' ->1
select COUNT(*) from #temp where a=' a' ->1
select COUNT(*) from #temp where a like ' a ' ->0 (important ..)
drop table #temp
So storage is done by
trimming trailing spaces when ANSI_PADDING is OFF.