CODEWITHSUNDEEP
asp.netsql-server-2008-r2
hoestang406
hoestang406

Reputation: 31

Comparing NVarchar fields In SQL Server 2008 R2

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.

Upvotes: 3

Views: 1822

Answers (3)

Atron Seige
Atron Seige

Reputation: 3059

I am experiencing the same problem.

My table contains an NVARCHAR(MAX) field called Definition. The current string in the database is 956 chars in length.

The code calls the data and valid data is returned. [Stuff happens]. The update command is built and it uses the Definition field in the WHERE clause. Zero rows are updated.

I intercepted the query and noticed that when the Definition is excluded from the where, the UPDATE command executes.

Going to the DB directly, I ran a simple SELECT Definition FROM Table WHERE Id = [whatever the id is]. Row is returned.

Next, I copied the Definition from the first result and altered the query to WHERE Definition = '[...]' and nothing is returned.

Begin confused, I altered the query so that an inner select gets the definition by Id, and the outer has an IN on the result of the inner select. That returns a row.

This leads me to think that the "problem" occurs when you compare text(ASCII) to the value in NVARCHAR (UNICODE).

I am still trying to get the answer.

*Please do not comment on the fact that I am builing queries in code or the strange WHERE clause.

Upvotes: 0

Aristos
Aristos

Reputation: 66641

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"

Upvotes: 2

Andriy M
Andriy M

Reputation: 77707

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.

Upvotes: 1

Related Questions