Weird WHERE Clause Behavior. Why does this return a row?

In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example?

WITH SRC AS (SELECT cast('12345' as varchar) DEMO)
SELECT * FROM SRC WHERE DEMO='12345  '

What if one needs to query to find '1234 ' but not '1234'

What is happening is that SQL pads spaces to the end of strings so that they are of the same length. So if you try do something like:

SELECT 1 WHERE '' = ' '

You will actually get 1 back even though they are not the same. However, if you do something like:

SELECT 1 WHERE 'a' = ' a'

It will not return anything as it would be comparing 'a ' to ' a' which do not match.

However, this would return 1:

SELECT 1 WHERE 'a' = 'a '

As it is comparing 'a ' to 'a '

StackExchange Database Administrators Q#138800, answer score: 10