SQL SERVER sorting rules use the characteristics of sorting rules to calculate Chinese Character strokes and obtain the first letter of Pinyin)

SQL SERVER sorting rules are usually not used many times, and many beginners may be unfamiliar, but some
A common error occurs when you query a database that is connected to multiple tables in different databases.
If the default Character Set of the library is different, the system will return the following error:

"The sorting rule conflict for equal to operations cannot be resolved ."

I. Error Analysis:
This error is caused by inconsistent sorting rules. Let's perform a test, for example:
Create table # t1 (
Name varchar (20) collate Albanian_CI_AI_WS,
Value int)

Create table # t2 (
Name varchar (20) collate Chinese_PRC_CI_AI_WS,
Value int)

After the table is created, execute the connection query:

Select * from # t1 A inner join # t2 B on A. name = B. name

In this way, the error occurs:

Server: Message 446, level 16, status 9, Row 1
The sorting rule conflict of the equal to operation cannot be solved.
To eliminate this error, the simplest way is to specify its sorting rules when the table is connected.
No longer appears. The statement is written as follows:

Select *
From # t1 A inner join # t2 B
On A. name = B. name collate Chinese_PRC_CI_AI_WS

Ii. Sorting rule introduction:

What is a sorting rule? "In Microsoft SQL Server 2000,
The physical storage of strings is controlled by sorting rules. Sorting rules specify the bit mode and storage of each character
Rules Used for saving and comparing characters. "
Run the following statement in the query analyzer to obtain all the sorting rules supported by SQL SERVER.

Select * from: fn_helpcollations ()

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.
For example:
Chinese_PRC_CS_AI_WS
First half: the UNICODE character set. The Chinese_PRC _ pointer sorts UNICODE in simplified Chinese characters.
The second half of the sorting rule is the suffix meaning:
_ BIN binary sorting
_ CI (CS) is case sensitive, CI is case insensitive, and CS is case sensitive
_ Whether AI (AS) distinguishes stress, AI does not distinguish,
_ KI (KS) indicates whether Kana is distinguished. KI is not distinguished, and KS is distinguished.
_ Whether WI (WS) is differentiated by width WI and WS

Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.
Accent differentiation: select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected,
Comparison also treats letters with different accents as unequal.
Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.
Width differentiation: select this option if you want to make the comparison between halfwidth and fullwidth characters.

Iii. Application of sorting rules:
SQL server provides a large number of sorting rules for WINDOWS and SQL SERVER, but its applications often
Ignored by developers. In fact, it is of great use in practice.

Example 1: sort the content of the table NAME column in pinyin order:

Create table # t (id int, name varchar (20 ))
Insert # t select 1, '中'
Union all select 2, 'status'
Union all select 3, 'people'
Union all select 4, 'A'

Select * from # t order by name collate Chinese_PRC_CS_AS_KS_WS
Drop table # t
/* Result:
Id name
-------------------------------
4
2 countries
3 persons
Medium
*/

Example 2: sort the content of the table NAME column by the last NAME strokes:

Create table # t (id int, name varchar (20 ))

Insert # t select 1, '3'
Union all select 2, 'B'
Union all select 3, '2'
Union all select 4, '1'
Union all select 5, '10'
Select * from # t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS
Drop table # t
/* Result:
Id name
-------------------------------
4yi
2 B
3 II
5th
1 3
*/

4. Application extension of sorting rules in practice
SQL SERVER Chinese Character sorting rules can be sorted by pinyin, strokes, etc. How can we use this function?
To solve Chinese Character problems? Here is an example:

Calculate Chinese Character strokes based on the characteristics of sorting rules

To calculate Chinese Character strokes, we must first prepare for the computation. We know that WINDOWS has many Chinese characters and UNICODE currently
A total of 20902 Chinese characters are included. The UNICODE value of the simplified GBK code starts from 19968.
First, we first use SQL Server to obtain all Chinese characters without dictionary. We simply use SQL statements
You can get:

Select top 20902 code = identity (int, 19968,1) into # t from syscolumns a, syscolumns B

Use the following statement to obtain all Chinese characters sorted by UNICODE values:

Select code, nchar (code) as CNWord from # t

Then, we use the SELECT statement to sort it by strokes.

Select code, nchar (code) as CNWord
From # t
Order by nchar (code) collate Chinese_PRC_Stroke_CS_AS_KS_WS, code

Result:
Code CNWord
-----------------
19968 I
20008 bytes
20022
20031 bytes
20032 bytes
20033 bytes
20057 B
20058 bytes
20059 bytes
20101 bytes
19969 ding
..........

From the above results, we can clearly see that the code for a Chinese character ranges from 19968 to 20101, from small to large,
The first word of the two Chinese characters is "ding", and the CODE is 19969, so it will not start again in order. With this result, we can easily
Use SQL statements to obtain the first or last Chinese character of each stroke.
The following statement is used to obtain the last Chinese character:

Create table # t1 (id int identity, code int, cnword nvarchar (2 ))

Insert # t1 (code, cnword)
Select code, nchar (code) as CNWord from # t
Order by nchar (code) collate Chinese_PRC_Stroke_CS_AS_KS_WS, code

Select A. cnword
From # t1
Left join # t1 B on A. id = B. id-1 and A. code <B. code
Where B. code is null
Order by A. id

Obtain 36 Chinese characters. Each Chinese character is sorted by Chinese_PRC_Stroke_CS_AS_KS_WS sorting rule.
Last Chinese character:

Ma fenglongqi

As shown above, "marker" is the last word after sorting all Chinese characters. "marker" is the last word after sorting all the two Chinese characters.
A word... and so on.
However, it was also found that the strokes behind the 33rd Chinese character "33 strokes" were messy and incorrect. But it doesn't matter. It's better than "success" strokes.
There are only four Chinese characters. We manually add: 35, 36, 39, and 64.

Create a Chinese character stroke table (TAB_HZBH ):
Create table tab_hzbh (id int identity, cnword nchar (1 ))
-- Insert the first 33 Chinese Characters
Insert tab_hzbh
Select top 33 A. cnword
From # t1
Left join # t1 B on A. id = B. id-1 and
-----------------