SQL full-width character and half-width character check

Source: Internet
Author: User
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 %'

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.