CODEWITHSUNDEEP
sql-server-2008whitespace
Patrick M
Patrick M

Reputation: 166

Sql server string comparison problem with chinese white-space

I have a record in my sql server database with the following value for a nvarchar column: '穂 坂' The second character is the Unicode value 0x20 which is a simple space character.

There is a unique key constraint on that column.

I get a unique key violation when I try to insert the following value: '穂　坂' In this string, the second character is the Unicode value 0x3000, which is a chinese space character.

Why do I get the unique key violation? Why does sql server "convert" the chinese white-space character to a simple space?

Thanks in advance for any insights! My collation is SQL_Latin1_General_CP1_CI_AS by the way.

Upvotes: 2

Views: 1547

Answers (2)

SQLpro
SQLpro

Reputation: 11

First you may use the approriate string type in SQL including the collation. Second you may use UNICODE strings beginning by N prefix.

Example:

SELECT CAST(N'穂 坂' COLLATE Chinese_Simplified_Pinyin_100_BIN2 AS VARBINARY(32))

=> 0x427A20004257

SELECT CAST(N'穂　坂' COLLATE Chinese_Simplified_Pinyin_100_BIN2 AS VARBINARY(32))

=> 0x427A00304257

Upvotes: 1

p.campbell
p.campbell

Reputation: 100637

It could be that your collation is width-insensitive.

Consider:

  • changing your database's collation to one that fits your needs, and it width-sensitive.
  • change the collation on that particular column to one that is width-sensitive. This could/will lead to more COLLATE clauses throughout your stored procs, etc. Not sure if this is a recommended practice by DBAs.

alt text

Upvotes: 0

Related Questions