Like operator and Trailing spaces in SQL Server

Question

This one matches column_name like 'CharEndsHere%'

and

This one doesn't column_name like 'CharEndsHere'

I know that like operator will consider even the trailing spaces, so I just copied the exact column value (with trailing spaces) and pasted it.

Something like column_name like 'CharEndsHere ' yet it doesn't match -- why?.

I haven't used '=' operator since the columns type is ntext

Is there something I am missing here or shouldn't I use like operator in this way?

Edited : column_name like 'CharEndsHere__' (__ denoted the spaces) 'CharEndsHere ' is the exact value in that cell, using like in this way valid or no?

Edit :

This is the code I tried,

SELECT * 
FROM [DBName].[dbo].[TableName] 
WHERE [DBName].[dbo].[TableName].Address1 LIKE rtrim('4379 Susquehanna Trail S  ') 

I have also tried without using rtrim, yet the same result

Edit: According to Blindy's answer,

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.

But in my case, I have queried, Like 'abc' and there is a cell containing 'abc '(with trailing spaces) which is not returned. That's my actual problem

Answer 1

This is a case of reading the documentation, it's very explicitly stated here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

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.

Edit: According to your comments, you seem to be looking for a way to use like while ignoring trailing spaces. Use something like this: field like rtrim('abc '). It will still use indexes because rtrim() is a scalar operand and it's evaluated before the lookup phase.