sql query doesnt return correct rows

Question

I have a table named 1 with these records:

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

(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='1AĳS0' AND one='1AĳS0'

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

is it utf8 character set? try using COLLATE with your querry. Using COLLATE in SQL Statements — bansi
– bansi, Commented Aug 11, 2013 at 4:28
when you are using unicode you need to prefix your string with N for example: WHERE two = N'6Aࡀ㐠㗨㙡㙡⻑S0S0S0S0S0S0' — VahidNaderi
– VahidNaderi, Commented Aug 11, 2013 at 4:35

Marc Gravell · Accepted Answer · 2013-08-11 08:30:33Z

1

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);

edited Aug 11, 2013 at 8:30

answered Aug 11, 2013 at 8:01

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Feri Over a year ago

I tried prefix N but it has syntax error and didn't understand the second way

Marc Gravell Over a year ago

@Feri what exactly does the syntax error say? If (as per comments) this is sql server, then both of these are perfectly fine. The 2nd approach (parameters) should actually be how you write most of your SQL operations. If it isn't: then you really need to fix that!

Steve Kass · Accepted Answer · 2013-08-11 21:39:41Z

0

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

answered Aug 11, 2013 at 21:39

Steve Kass

7,22422 silver badges28 bronze badges

2 Comments

Feri Over a year ago

Thanks for helping but I didn't understand your codes well.Can you explain them?

Steve Kass Over a year ago

This is just an example to show that the collation can affect the result of comparing strings with =. You should do some reading about character collations (look for COLLATE in Books Online). If you have a specific question about part of this example. It's not practical to try to explain the whole thing without knowing what you are confused by.

Collectives™ on Stack Overflow

sql query doesnt return correct rows

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related