Comparing NVarchar fields In SQL Server 2008 R2

Question

I have 2 fields in a table declared as nvarchar(50). On a few records the field has what appears to be the same value. Howvever when I run a query with the where clause field1 <> field2, it picks up that the fields are different. I tried using the where clause ltrim(rtrim(field1)) <> ltrim(rtrimfield2)) and it still finds a difference. I'm stumped!

Thanks in advance.

Answer 1

This can happens when you use similar letter from different languages. You can not recognize them on eye, but are different...

Example, this is all letter in English and Ελληνικά (Ellas), some seems the same, but are not.

o <> ο
Y <> Υ
A <> Α
E <> Ε

some other are have small but noticeable different
u <> υ
p <> ρ
i <> ι

So this 'word' is different from this 'wοrd' on 'o', but you can not see it.

ps I think this issue, because you say "in few records"

Answer 2

In addition to what @Aristos says, it could also be characters like non-breaking space (U+00A0) or zero-width space (U+200B), which are not trimmed by LTRIM/RTRIM.