How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?

Question

If I deliberately store trailing spaces in a VARCHAR column, how can I force SQL Server to see the data as mismatch?

SELECT 'foo' WHERE 'bar' = 'bar    '

I have tried:

SELECT 'foo' WHERE LEN('bar') = LEN('bar    ')

One method I've seen floated is to append a specific character to the end of every string then strip it back out for my presentation... but this seems pretty silly.

Is there a method I've overlooked?

I've noticed that it does not apply to leading spaces so perhaps I run a function which inverts the character order before the compare.... problem is that this makes the query unSARGable....

Answer 1

ansi padding http://msdn.microsoft.com/en-us/library/ms187403.aspx

Answer 2

Like you said, I don't think there are many options. The only two I could come up with were these:

DECLARE @x nvarchar(50)
DECLARE @y nvarchar(50)
SET @x = 'CAT     '
SET @y = 'CAT'

SELECT 1 WHERE len(@x + '_') = len(@y + '_')

SELECT 1 WHERE reverse(@x) = reverse(@y)

EDIT

Thought of a third:

SELECT 1 WHERE REPLACE(@x, ' ', '_') = REPLACE(@y, ' ', '_')

And a fourth, assuming you're on SQL 2005+

SELECT 1 WHERE QUOTENAME(@x) = QUOTENAME(@y)

Personally, I like the reverse idea the best, but it all depends on which one performs best for you.

Answer 3

SQL Server sees them differently.

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.
To return the number of bytes used to represent an expression, use the DATALENGTH function

Also you better be aware that SQL Server follows ANSI/ISO SQL-92 padding the character strings used in comparisons so that their lengths match before comparing them.

Update (later):
I deleted my code using LIKE (which does not pad spaces during comparison) and DATALENGTH() since there engagement is not foolprof comparison of strings.

Anyway, this question is duplicate of a few previous ones in SO, containing answers, for ex., this one. And there are others (I did not read out them for exhaystive answers) this , this, and others. The search finds dozens in SO and zillions in internet. I dunno how it was possible to miss the answer.

Answer 4

I've only really got two suggestions. One would be to revisit the design that requires you to store trailing spaces - they're always a pain to deal with in SQL.

The second (given your SARG-able comments) would be to add acomputed column to the table that stores the length, and add this column to appropriate indexes. That way, at least, the length comparison should be SARG-able.

Answer 5

you could try somethign like this:

declare @a varchar(10), @b varchar(10)
set @a='foo'
set @b='foo   '

select @a, @b, DATALENGTH(@a), DATALENGTH(@b)