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

user_369775 0
Viewed 12094

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....

varchar sql-server
6 Answers

Sometimes the dumbest solution is the best:

SELECT 'foo' WHERE 'bar' + 'x' = 'bar     ' + 'x'

So basically append any character to both strings before making the comparison.

user_66805210
Related