Sql server string comparison problem with space in chinese space
I have an entry in my sql server database with the following value for the nvarchar column: '穂 坂' The second character is the Unicode value 0x20 which is a simple space character.
This column has a unique key constraint.
I get a unique key violation when I try to insert the following value: '穂 坂' In this line, the second character is the Unicode value 0x3000, which is the Chinese space character.
Why am I getting a unique key violation? Why is sql server "converting" Chinese space character to plain space?
Thanks in advance for any ideas! By the way, my collation is SQL_Latin1_General_CP1_CI_AS.
a source to share
You can use the approriate string type in SQL first, including sorting. Second, you can use UNICODE strings starting with the 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
a source to share
Perhaps your mapping is width-insensitive .
Consider:
- changing the database configuration to suit your needs and width sensitivity.
- change the sort on that specific column to a width sensitive value. This may / will result in more COLLATE clauses throughout all stored processes, etc. Not sure if this is the recommended practice for DBAs.
a source to share