Sort SQL Server's sorting rules
Sort SQL Server's sorting rules
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 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 A. code Where B. code is null
Order by A. id
-- Add the last four Chinese Characters
Set identity_insert tab_hzbh on
Go
Insert tab_hzbh (id, cnword)
Select 35, N 'hour'
Union all select 36, N 'hour'
Union all select 39, N 'hour'
Union all select 64, N 'hour'
Go
Set identity_insert tab_hzbh off
Go
So far, we can get the result. For example, we want to get the Chinese character "country" strokes:
Declare @ a nchar (1)
Set @ a = 'Guo'
Select top 1 id
From tab_hzbh
Where cnword >=@ a collate Chinese_PRC_Stroke_CS_AS_KS_WS
Order by id
Id
-----------
8
(Result: the number of strokes in the Chinese "country" is 8)
All the above preparation processes are only used to write the following function. This function disconnects all the temporary tables and
For general purpose and code transfer convenience, the tab_hzbh table content is written in the statement, and a string of user input is calculated.
Total strokes of Chinese characters:
Create function fun_getbh (@ str nvarchar (4000 ))
Returns int
As
Begin
Declare @ word nchar (1), @ n int
Set @ n = 0
While len (@ str)> 0
Begin
Set @ word = left (@ str, 1)
-- If it is not a Chinese character, the stroke count is 0.
Set @ n = @ n + (case when unicode (@ word) between 19968 and 19968 + 20901
Then (select top 1 id from (
Select 1 as id, N 'region' as word
Union all select 2, N 'hour'
Union all select 3, N 'mar'
Union all select 4, N 'wind'
Union all select 5, N 'Long'
Union all select 6, N 'qy'
Union all select 7, N 'turtles'
Union all select 8, N 'tooth'
Union all select 9, N 'hour'
Union all select 10, N 'hour'
Union all select 11, N 'cores'
Union all select 12, N 'hour'
Union all select 13, N 'hour'
Union all select 14, N''
Union all select 15, N 'hour'
Union all select 16, N 'hour'
Union all select 17, N 'hour'
Union all select 18, N 'hour'
Union all select 19, N 'hour'
Union all select 20, N 'hour'
Union all select 21, N 'hour'
Union all select 22, N 'hour'
Union all select 23, N 'hour'
Union all select 24, N 'hour'
Union all select 25, N 'hour'
Union all select 26, N 'hour'
Union all select 27, N 'hour'
Union all select 28, N 'hour'
Union all select 29, N 'hour'
Union all select 30, N 'hour'
Union all select 31, N 'hour'
Union all select 32, N 'hour'
Union all select 33, N 'hour'
Union all select 35, N 'hour'
Union all select 36, N 'hour'
Union all select 39, N 'hour'
Union all select 64, N 'hour'
) T
Where word >=@ word collate Chinese_PRC_Stroke_CS_AS_KS_WS
Order by id ASC) else 0 end)
Set @ str = right (@ str, len (@ str)-1)
End
Return @ n
End
-- Function call instance:
Select dbo. fun_getbh ('People 'S republic of china '), dbo. fun_getbh ('Central People's Republic of China ')
Execution result: the total number of strokes is 39 and 46, both in simplified and Traditional Chinese.
Of course, you can also change the Chinese characters and strokes in the above "union all" to a fixed table.
Create clustered index for the column, and set the column sorting rule:
Chinese_PRC_Stroke_CS_AS_KS_WS
This is faster. If you are using a BIG5 code operating system, you have to generate additional Chinese characters in the same way.
However, remember that these Chinese characters are selected using SQL statements rather than manually entered.
It is obtained by searching the dictionary, because the Xinhua Dictionary is different from the UNICODE Character Set after all, and the result of searching the dictionary will be incorrect.
Yes.
Obtain the first letter of the Chinese pinyin alphabet using the sorting rule feature.
Using the same total number of strokes, we can also write a function to calculate the first letter of Chinese pinyin. As follows:
Create function fun_getPY (@ str nvarchar (4000 ))
Returns nvarchar (4000)
As
Begin
Declare @ word nchar (1), @ PY nvarchar (4000)
Set @ PY =''
While len (@ str)> 0
Begin
Set @ word = left (@ str, 1)
-- If it is not a Chinese character, the original character is returned.
Set @ PY = @ PY + (case when unicode (@ word) between 19968 and 19968 + 20901
Then (select top 1 PY from (
Select 'A' as PY, n' comment 'as word
Union all select 'B', n''
Union all select 'C', n' then'
Union all select 'D', N 'region'
Union all select 'E', n' then'
Union all select 'F', n' then'
Union all select 'G', n' then'
Union all select 'h', N 'hour'
Union all select 'J', n' else'
Union all select 'k', n' then'
Union all select 'l', n' then'
Union all select 'M', n' then'
Union all select 'n', n' then'
Union all select 'O', n' then'
Union all select 'P', n' exposure'
Union all select 'Q', n' then'
Union all select 'R', N 'region'
Union all select's ', N 'region'
Union all select 'T', n' then'
Union all select 'w', N 'hangzhou'
Union all select 'x', n' then'
Union all select 'y', n' then'
Union all select 'Z', n' else'
) T
Where word >=@ word collate Chinese_PRC_CS_AS_KS_WS
Order by py asc) else @ word end)
Set @ str = right (@ str, len (@ str)-1)
End
Return @ PY
End
-- Function call instance:
Select dbo. fun_getPY ('People 'S republic of china '), dbo. fun_getPY ('Central People's Republic of China ')
All results are: ZHRMGHG
If you are interested, you can use the same method to extend it to a function for getting all Chinese characters, and you can even get a full read.
Tone, but most of the categories are full. It is best to use a comparison table to get a full search. The search speed for more than 20 thousand Chinese characters is very fast.
The table can also make full use of the table index.
There are many other clever uses of sorting rules. You are welcome to discuss it together.
