Shulou(Shulou.com)05/31 Report--

This article to share with you is about SQL SERVER which sorting rules, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

The same is true with graphical and Insert statements, as well as with query analyzer operations.

In addition to the rule of entry errors, the rest can be explained is the sorting rules of SQLSERVER. The collation used in SQLSEVER2005 in our test environment is SQL_Latin1_General_CP1_CI_AS, instead of the three collations that correctly display simplified Chinese characters: Chinese_PRC_BIN,Chinese_PRC_CI_AS,Chinese_PRC_CS_AS

In SQLSERVER2005, change the collation to Chinese_PRC_CI_AS, and the problem is solved.

MS is described as follows: "In Microsoft SQL Server, the physical storage of strings is governed by collations. Collation specifies the bit pattern that represents each character and the rules used to store and compare characters. "

That is, in SQL SERVER, collations are actually character encodings. (I don't know why MS came up with the word sorting rule.)

Execute the following statement in Query Analyzer to get all the collations supported by SQL SERVER.

[sql]

select * from ::fn_helpcollations()

A collation name consists of two parts, the first half of which refers to the character set supported by the collation.

For example: Chinese_PRC_CS_AI_WS

First half: refers to UNICODE character set, Chinese_PRC_refers to collation rules for mainland simplified characters UNICODE.

The second half of the collation is the suffix meaning:

_BIN Binary Sort

_CI(CS) case-sensitive, CI insensitive, CS sensitive

_AI(AS) whether to distinguish stress, AI does not distinguish, AS distinguishes

_KI(KS) Distinguish kana type,KI does not distinguish, KS distinguishes

_WI(WS) Whether to distinguish width WI does not distinguish, WS distinguishes

Case sensitive: Select this option if you want the comparison to treat upper and lower case letters as unequal.

Stress differentiation: Select this option if you want the comparison to treat stressed and unstressed letters as unequal. If this option is selected, the comparison also treats letters with different accents as unequal.

Distinguish Kana: Select this option if you want the comparison to treat Katakana and Hiragana Japanese syllables as unequal.

Distinguish Width: Select this option if you want the comparison to treat half-width and full-width characters as unequal

The conclusions are as follows:

For Chinese character display, At the same time, if you don't want to change the sorting rules on, and you want to display Chinese characters correctly, it is recommended to use Unicode type fields all the time, that is, those field types that start with N, such as nChar,nVarchar, to display Chinese characters correctly.

If you don't want to change the sorting rules and don't want to change the field type, you have to change the SQL statement. For all Chinese characters, you must add N before it can be displayed correctly. Please refer to the following two sentences for specific methods:

Inquiry:

[sql]

select * from ff_ordering where wording1 =N'Chinese'

Insert:

[sql]

insert ff_ordering (wording1, wording2,remark1) values(N'Chinese',N'Chinese', N' remark 2')

The above is what sort rules are in SQL SERVER, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.