SQL Server String Comparison with Trailing White Space

AKA: When “=” does not mean “=”

Sounds a bit silly, but it causes a lot of confusion for developers.  Take the following example in SQL Server:

declare @t nvarchar(20) = 'aaaa   '
declare @t2 nvarchar(20) = 'aaaa'

if(@t=@t2)
	print 'yes'
else
	print 'no'

Are you expecting the output to be “no”?  The output will be “yes”, contrary to what most developers expect.  Often, someone will perform a comparison to remove whitespace and perform a check such as:

if(data!=rtrim(data))

This will not produce the expected results, however, since SQL Server is Width Insensitive by default.  The whitespace is essentially trimmed off during this comparison, event when rtrim() is not used. 

The problem here, is that there is no SQL Server collation name that IS width sensitive.  In order to resolve this problem, you need to perform a length comparison…but pick the right one.

declare @t nvarchar(20) = 'aaaa   '
declare @t2 nvarchar(20) = 'aaaa'

select len(@t)
select len(@t2)

select datalength(@t)
select datalength(@t2)

As books online indicates LEN() “Returns the number of characters of the specified string expression, excluding trailing blanks. “.  DATALENGTH(), however, will keep the trailing spaces and take them into account when returning the number of bytes in the expression.  One thing to note here is that if you are dealing with CHAR instead of VARCHAR data, your DATALENGTH() will be fixed for both values to the same length (20 in this example, 40 if NCHAR).

The majority of cases dealing with strings may not have to deal with trailing white space.  But, if you happen upon a scenario where you need to check for equality inclusive of white space, this is an important distinction to have knowledge of.