SQL full-width character and half-width character check

Problems encountered in the project. The comparison results of the half-width character F00000 and the full-width character F00000 are the same, leading to duplicate primary key issues. The full-width ASCII code is 0xA3A8, while the half-width is 0x28. So why does SQL Server think it is the same?

In fact, the problem lies in the database sorting rules. Take a closer look at the documents of SQL Server. The sorting rules of SQL Server are composed of the following parts: code page, case-sensitive, stress-sensitive, and width-sensitive. The last one is not mentioned in the SQL Server online help. In fact, the problem encountered in this article is caused by this reason. Width differentiation: specify that SQL Server differentiates single-byte notation (halfwidth) and double-byte notation (fullwidth) of the same character ). If not selected, SQL Server considers the single-byte representation of the same character as the dual-byte representation.

By default, When you install the Chinese version of SQL Server, the sorting rules selected by SQL Server are Chinese_PRC_CI_AS (Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive ), it is a Chinese character, not case sensitive, accent sensitive, not Kana, not width sensitive. Therefore, we naturally agree that F00000 = F00000; therefore, the correct choice should be the Chinese sorting rule suffixed with WS.

In this example, we should select Chinese_PRC_CI_AS_WS.

Let's take a look. What happens after the specified sorting rule is Chinese_PRC_CI_AS_WS?

Select 1 where 'f00000' = 'f00000'

Collate Chinese_PRC_CI_AS_WS

(The number of affected rows is 0)

Important:

How can I check which sorting rule is used? You can use the following SQL statement.

SELECT *

FROM: fn_helpcollations ()

Query the information of all sorting rules.

Find information about all Chinese sorting rules

SELECT * FROM

(

SELECT *

FROM: fn_helpcollations ()

WHERE name like 'Chinese %'