Sometimes it can happen, that you compare two strings, which looks exactly sames, but they are processed as not equal. Also when you use SELECT DISTINCT and still there are rows that looks same, like DISTINCT is not working. Dimensional attribute in OLAP can return errors for duplicities even you used DISTINCT for dimension. This is the right time to search invisible/hidden characters in string.  RTrim and LTrim function not stripping whitespace.

First you have to find if there are invisible characters. The easiest way is to check strings on binary base. Use this query

SELECT [YourColumn], CAST([YourColumn] as varbinary(max)) FROM [YourTable]

Now you can see where is the different between two same looking strings.

Functions RTrim and LTrim function not stripping whitespace when there are different hidden characters then whitespace. Problematic characters like Horizontal tab, Line Feed etc. must be removed different way.

Easiest way is replace hidden characters by whitespace or empty space if it's possible and then trim string.

UPDATE [YourTable] 
SET
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([YourColumn], CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), CHAR(160), '')))

Becase whitespace are not really space like ' ' 

CHAR(9)   = Horizontal Tab
CHAR(10)  = Line Feed
CHAR(13)  = Carriage Return
CHAR(160) = Non-Breaking Space

You can replace them to ' ' or Char(32), it depends how you want to handle it.

There exists more hidden character, but these are the most often.

Sources:
What's the best way to identify hidden characters in the result of a query in SQL Server (Query Analyzer)?
SQL Server RTRIM(LTRIM([City])) not stripping whitespace

Tweet