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

In this issue, the editor will bring you about the sorting rules in the SQLServer database. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

What are the sorting rules of SQLServer2005 database

After excluding typing errors, all that can be explained is the collation of SQLServer. The collation used by friends in SQLSEVER2005 is SQL_Latin1_General_CP1_CI_AS, rather than three collation rules that can correctly display simplified Chinese characters: Chinese_PRC_BIN,Chinese_PRC_CI_AS,Chinese_PRC_CS_AS

After changing the collation to Chinese_PRC_CI_AS in SQLServer2005, the problem is solved.

MS's description of collation: "in MicrosoftSQLServer2000, the physical storage of strings is controlled by collation, which specifies the bit pattern that represents each character and the rules used to store and compare characters." That is, in SQLServer, collation is actually character encoding.

Execute the following statement within the query analyzer to get all the collations supported by SQLServer.

Select*from::fn_helpcollations ()

The collation name consists of two parts, and the first half refers to the character set supported by this collation. Such as: Chinese_PRC_CS_AI_WS. The first half: refers to the UNICODE character set, and Chinese_PRC_ refers to the sorting rules for mainland simplified characters UNICODE.

What are the sorting rules of SQLServer2005 database

The second half of the collation is the suffix meaning:

_ BIN binary sort

Whether CI (CS) is case-sensitive, CI is not case-sensitive, CS is case-sensitive

Whether _ AI (AS) distinguishes stress, AI does not distinguish, AS distinguishes

Whether _ KI (KS) distinguishes katakana types, KI does not distinguish, KS distinguishes

Whether _ WI (WS) distinguishes width WI does not distinguish, WS distinguishes

Case sensitive: select this option if you want the comparison to treat uppercase and lowercase letters as unequal.

Distinguish stress: select this option if you want the comparison to treat accented and unstressed letters as unequal. If you select this option, the comparison also treats letters with different stresses as unequal.

Distinguish katakana: select this option if you want the comparison to treat Katakana and katakana Japanese syllables as unequal.

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

Conclusion:

One: for the existence of Chinese character display, while do not want to change the sorting rules on the database, but also want to display Chinese characters correctly, it is recommended that all Unicode type fields be used in the design, that is, those field types that begin with N, such as nChar,nVarchar, can display Chinese characters correctly.

Second: if you do not want to change the sorting rules, and do not want to change the field type, then you have to change the SQL statement, for all Chinese characters, you have to add N before it can be displayed correctly. For specific methods, please refer to the following two statements:

Inquiry: select*fromtb_CustwhereFirstName=N' Wang'

Insert: inserttb_Cust (FirstName,LastName,Sex) values (N 'Wang', N 'Xinhao', N 'male')

These are the sorting rules in the SQLServer database shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to 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.