CODEWITHSUNDEEP
c#sql
Feri
Feri

Reputation: 1111

sql query doesnt return correct rows

I have a table named 1 with these records:

ID  one          two                  three
1: 1AijS0 - 6Aݨ⻈㗨㙡㙡⻑S0S0S0S0S0S0 - 1AijS0
2: 1AijS0 - 6Aࡀ㐠㗨㙡㙡⻑S0S0S0S0S0S0 - 1AijS0

(note that one and three columns of both records have 5 characters and are same and two column has 20 characters but are not same)

here I have a problems:

when I use this delete command (no matter where) both rows are deleted (although the correct row is the second):

DELETE FROM [1] WHERE two='6Aࡀ㐠㗨㙡㙡⻑S0S0S0S0S0S0' AND three='1AijS0' AND one='1AijS0'

I have not seen the second problem in other examples of records.

Upvotes: 1

Views: 166

Answers (2)

Steve Kass
Steve Kass

Reputation: 7184

The result of a string comparison depends on a character set collation. By default, this is the collation that was specified when the server instance was installed, but collations can be specified at the database and table column levels also. They can also be specified in the query. Collations can apply to comparisons and to individual strings, and there are many rules about collation compatibility.

The strings in your example are considered equal under the collation used when you run the query.

There is no easy answer to what collation to use if you have strings in various languages. If you want strings that look different to be considered unequal, you can try a binary collation. However, if you compare strings using a collation that's different from the system collation or the collation that applies because it was specified for the database or the table column, the query processor may not be able to use indexes, and your query may run much more slowly.

The interpretation of user-supplied strings may also depend on the operating system locale, and results might change depending on the use of the N that indicates Unicode.

Here is a demonstration of how the strings in your example might be considered equal under some collations and unequal under others:

with t(a,b) as (
  select
    N'6Aࡀ㐠㗨㙡㙡⻑S0S0S0S0S0S0',
    N'6Aݨ⻈㗨㙡㙡⻑S0S0S0S0S0S0'
)
  select
    'Japanese_CI_AI' as Collation,
    case when a=b collate Japanese_CI_AI then 'Equal' else 'Not Equal' end as Result,
    a, b
  from t
  union all
  select
    'Latin1_General_100_CS_AI' as Collation,
    case when a=b collate Latin1_General_100_CS_AI then 'Equal' else 'Not Equal' end as Result,
    a, b
  from t
  union all
  select
    'Arabic_100_CI_AS' as Collation,
    case when a=b collate Arabic_100_CI_AS then 'Equal' else 'Not Equal' end as Result,
    a, b
  from t
  union all
  select
    'SQL_Latin1_General_CP1_CI_AI' as Collation,
    case when a=b collate SQL_Latin1_General_CP1_CI_AI then 'Equal' else 'Not Equal' end as Result,
    a, b
  from t

union all select 'Latin1_General_BIN' as Collation, case when a=b collate Latin1_General_BIN then 'Equal' else 'Not Equal' end as Result, a, b from t

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062610

If the columns are nvarchar, you should be using N prefixes to indicate nvarchar literals...

SELECT * FROM [1] WHERE two=N'6Aݨ⻈㗨㙡㙡⻑...

But even better: use parameters, which avoids this issue and also avoids SQL injection at the same time:

SELECT * FROM [1] WHERE two=@two

and just add the parameter:

string two = ... // the value you want
cmd.Parameters.AddWithValue("two", two);

Upvotes: 1

Related Questions