The amazing TRAILING SPACE theory

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 and matching is done exactly as it is done for ASCII.

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.