Comparison of Full-width and half-width characters in SQL Server

server| Comparison | problem
/*------------------------------------------------------------------------------------

/* Welcome to reprint, please retain this declaration information

/* Author: Ongyan enhydraboy@yahoo.com.cn

/*------------------------------------------------------------------------------------

This is a problem that I have encountered in my actual project. The results of 03bhl01001 (Shanghai) and 03bhl01001 (Shanghai) are the same. Causes the duplicate primary key problem that is caused.

The difference between 03bhl01001 (Shanghai) and 03bhl01001 (Shanghai) is that the former bracket is a full-width bracket character, and the latter is a half-width bracket character. The full-width parenthesis character and the Half-width bracket character of the ASCII code are obviously not the same. Full-width (ASCII code is 0XA3A8, and Half-width (is 0x28). So why does SQL Server think it's the same?

The problem is actually the collation of the database, so let's look at the SQL Server documentation. SQL Server collation consists of several parts, code pages, case-sensitive, accent-sensitive, and width-sensitive. The last one is not mentioned further in the SQL Server online Help, but the problem that this article encounters is caused by this reason. Width sensitive: Specifies that SQL Server distinguishes between single-byte representations (half-width) and Double-byte representations (full-width) for the same characters. If there is no selection, SQL Server will consider the same character as a single-byte representation and a double-byte notation.

By default, when you install the Chinese version of SQL Server, the sort rules that SQL Server chooses for you are chinese_prc_ci_as (CHINESE-PRC, case-insensitive, accent-sensitive, Kanatype-insensitive, Width-insensitive, is Chinese, case-insensitive, accent-insensitive, kana-insensitive, width-insensitive. Therefore, naturally agree with 03bhl01001 (Shanghai) =03bhl01001 (Shanghai).

Therefore, the correct choice should be that the suffix is the Chinese collation of WS. In this case we should choose Chinese_prc_ci_as_ws.

Let's take a look at what happens when you specify that the collation is chinese_prc_ci_as_ws.



Select 1 where ' 03bhl01001 (shanghai) ' = ' 03bhl01001 (Shanghai) '

Collate Chinese_prc_ci_as_ws



-----------



(The number of rows affected is 0 rows)

It seems that the problem has been solved.

Important NOTE:

How do you see the collation used? You can use the following SQL statement.

SELECT *

From:: Fn_helpcollations ()

You can query for all collation information.

Find out all the Chinese collation information

SELECT * FROM

(

SELECT *

From:: Fn_helpcollations ()) A

WHERE name like ' chinese% '