SQL Server's sorting rules are not a lot of usage, perhaps many beginners are more unfamiliar, but there is an error to come to: SQL Server database, when the query is connected to the library multi-table, if the two database default character sets The system will return such an error: ?????? ?????????? "Unable to solve the sorting rule conflict of the Equal TO operation."
I. Error Analysis: This error is caused by the unstruction rules. We do a test, such as: 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 built, the connection query is performed:
Select * from # T1 a inner join # T2 b on a.name = B.Name
In this way, the error will appear:
?????????? server: message 446, level 16, state 9, row 1 ????????? Unable to solve the sorting rule conflict of the Equal TO operation. To exclude this error, the easiest way is to specify its sort rules when the table is connected, so that the error will no longer appear. Write this sentence:
Select * from # T1 a inner join # T2 b on a.name = B.NAME collate chinese_prc_ci_ai_ws
2. Sort Rules Description:
??? What is the sorting rule? MS is described in this: "In Microsoft SQL Server 2000, the physical storage of strings is controlled by the sort rule. Sorting rules specify the rules indicating the bit mode of each character and storage and comparison characters." In the query analyzer Execute the following statement, you can get all the sorting rules supported by SQL Server.
Select * from :: fn_helpcollations ()
The sorting rule name consists of two parts, and the first half refers to the character set supported by this sorting rule. As: Chinese_PRC_CS_AI_WS, the first half of the first half: refers to the Unicode character set, Chinese_PRC_ refers to the sorting rules for continental Simplifiedin Unicode. The second half of the sorting rules is the suffix meaning: _bin binary sort _CI (CS) is case sensitive, CI does not distinguish, whether the CS distinguishing _ai (AS) is distinguished, AI does not distinguish, AS distinguishing _ki (KS) Whether to distinguish between a false name, Ki does not distinguish, KS distinguish ??? _wi (ws) Differentiate the width Wi differentiate, WS distinction
Case sensitivity: If you want to make comparison, you should choose the uppercase letters and lowercase letters, please select this option. Distinguish the key: If you want to make the more accent and non-reconounced letters don't wait, select this option. If this option is selected, the letter is compared to the alphabetic alphabetically different. Distinguish the fake name: If you want to make the comparison to deepen the slice of pseudonym, please don't wait, select this option. Distinguishing the width: If you want to make the comparison to treat the half-angle character and full range of characters, please select this option.
III. Application of Sort Rules: SQL Server provides a large number of Windows and SQLServer dedicated sort rules, but its applications are often ignored by developers. In fact, it is useful in practice.
Example 1: Let the contents of the table Name column are sorted by Pinyin:
Create Table #t (ID INT, Name Varchar (20)) Insert #t SELECT 1, 'China Union All Select 2,' National '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 ???????????????? ???????? people 1 ?????????? * /
Example 2: Let the contents of the table name list according to the last name stroke:
Create Table #t (ID INT, Name Varchar (20))
INSERT #T SELECT 1, 'Three' Union All Select 2, 'B' Union All Select 3, 'Second' Union All Select 4, 'One' Union All Select 5, 'Ten' SELECT * FROM #t Order by Name Collate CHINESE_PRC_STROKE_CS_AS_KS_WS? DROP TABLE # T / * Result: ID ????????? Name ????????????????? ----------- ------------------ 4 ?????????? one 2 ?????????? 3 ??????? ??? two 5 ??????????? 10 1 ?????????? three * /
IV. Extended SQL Server Chinese characters in practice SQL Server Chinese characters can be sorted according to pinyin, strokes, so how do we use this feature to handle some of the challenges of Chinese characters? I will give an example now:
Calculate the Chinese character stroke with the characteristics of the sort rules
To calculate the Chinese character stroke, we have to prepare for work first, we know, Windows multi-country Chinese characters, Unicode is currently included in Chinese characters. The Simplified GBK code Chinese characters unicode values start from 19968. First, let's get all Chinese characters in the SQLServer method, don't have a dictionary, we can get the SQL statement you can get:
Select Top 20902 Code = Id, 19968, 1) INTO #T from syscolumns a, syscolumns b
Use the following statement, we get all Chinese characters, which is in order to follow the unicode value:
Select code, nchar (code) as cnword from #t
Then we use the SELECT statement to order it according to the stroke.
Select code, nchar (code) as cnword from #t Order by nchar (code) collate chinese_prc_stroke_cs_as_ks_ws, code
RESULTS: CODE ??????? cnWord ---------------- 19968 ?????? one 20008 ????? 丨 20022 ????? ? 丶 20031 ?????? 丿 20032 ?????? 乀 20033 ?????? 乁 20057 ?????? 乚 20059 ?????? 20101 ?????? 亅 19969 ?????? Ding ........
From the results, we can clearly see that a Chinese character, Code is from 19968 to 20101, from the small to the big row, but to the first word "Ding" of the two Chinese characters, Code is 19969, no Start according to the order. With this result, we can easily use the SQL statement to get the first or last Chinese character classified by each stroke Chinese character. The following uses the statement to get the last Chinese character: Create Table # T1 (ID Int Id Id Int Int, Cnword Nvarchar (2))
Insert # T1 (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 a left join # T1 b on a.id = B.ID-1 and A.code
Where b.code is null
Order by a.id
Get 36 Chinese characters, each Chinese character is the last Chinese character sorted by chinese_prc_stroke_cs_as_ks_ws sequencing rules:
亅阝马 风龙 鸩龀 鸩龀 鸩龀 龂 龂 龊 龊 龠厐 龠厐 麷 麷 龝齹 龝齹 龝齹 龝齹 齈 齈 齈
It can be seen above: "亅" is the last word after all Chinese characters sort, "阝" is the last word after all two Chinese characters ... Wait. But at the same time, it also found that the strokes behind the 33th Chinese characters "龗 (33)" are somewhat chaos, incorrect. But there is no relationship, there are only four Chinese characters than "龗" strokes, we hand together: 齾 35, 齉 36, 靐 39, 龘 64
Building a Chinese character stroke table (tab_hzbh): Create Table Tab_hzbh (ID Int IDITENTITY, CNWORD NCHAR (1)) - Put the first 33 Chinese character insert Tab_hzbhselect top 33 a.cnword from # 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 '齾'
Union all SELECT 36, N '齉'
Union all SELECT 39, N '靐'
Union all SELECT 64, N '龘'
Go
Set Identity_INSERT TAB_HZBH OFF
Go
So, we can get the result, such as we want to get the brush of the Chinese character "country":
Declare @a nchar (1) set @ a = 'country' SELECT TOP 1 ID from? Tab_hzbh where cnword> = @ a collate chinese_prc_stroke_cs_as_ks_wsorder by ID
ID ?????????? ----------- 8 (Result: Chinese characters "national" strokes are 8)
All of the above preparations, just to write the following functions, this function opens all temporary tables and fixed tables above, and writes the contents of the table tab_hzbh in the statement for universal and code transfer, and then calculates the user into a string of Chinese characters. Total stroke:
Create function fun_getbh (@str nvarchar (4000)) Returns Intasbendeclare @Word nchar (1), @ n tent @ n = 0WHILE LEN (@str)> 0beginset @ word = left (@ Str, 1) - if not Chinese characters, String 0 meter set @ n = @ n (Case When Unicode (@Word) Between 19968 and 19968 20901Then (SELECT TOP 1 ID, N '亅' AS Word Union All Select 2, N '阝'Union All Select 3, N' Ma 'Union All Select 4, N' Wind 'Union All Select 5, N' Dragon 'Union All Select 6, N' 'Union All Select 7, N' Turtle 'Union All Slect 8 , N 'tooth' union all SELECT 9, N '鸩' Union All Select 10, N '龀' union all SELECT 11, N '' union all SELECT 12, N '龂' Union All Select 13, N '龆' Union All SELECT 14, N 'Galid' Union All Select 15, N '龊' Union All Select 16, N 'Dragon' Union All Select 17, N '龠' Union All Select 18, N '厐' Union All Select 19, N 'Pang' Union All Select 20, N '龑' Union All Select 21, N '龡' Union All Select 22, N 'and' Union All SELECT 23, N '龝' Union All Select 24, N '齹' Union All SELECT 25, N '龣' Union All Select 26, N '龥' Union All Select 27, N '齈' Union All Select 28, N '龞' Union All Select 29, N '麷' Union All SELECT 30, N '鸾' unioo N All Select 31, N '麣' Union All SELECT 32, N '龖' Union All Select 33, N '龗' Union All Select 35, N '齾' Union All Slect 36, N '齉' Union All Select 39, N '靐' union all succ 64, n '龘') T where word> = @ word collate chinese_prc_stroke_cs_as_ks_wsorder by ID ASC) ELSE 0 END) SET @ Str = Right (@ Str, Len (@STR) -1) endreturn @ Nend
- Function call example: Select dbo.fun_getbh ('"People's Republic'), DBO.FUN_GETBH ('" People's Republic' ') Executive: The total number of strokes is 39 and 46, respectively. Of course, you can also exist in a fixed table in the Chinese characters and strokes in the "Union All" above, and the column sort rules are set to: chinese_prc_stroke_cs_as_ks_ws is faster. If you use a BIG5 code operating system, you have to generate Chinese characters, the method is the same. But one thing to remember: These Chinese characters are from SQL statement SELECT, not manual input, not to check the word code, because Xinhua Dictionary is different from Unicode character set, and the quotation of the dictionary will be incorrect.
????????? Take the characteristics of the rules to get Chinese characters Pinyin
Using the same method of the total number of strokes, we can also write a function of seeking Chinese characters in the first letters. as follows:
Create function fun_getpy (@str nvarchar (4000)) Returns nvarchar (4000) asbegindeclare @Word nchar (1), @ py nvarchar (4000) set @py = 'while g (@str)> 0beginset @ word = left (@ Str, 1) - If the Chinese character character, return to the original character set @ py = @ py (@Word) Between 19968 and 19968 20901Then (SELECT 'A' AS PY, N '骜 'As Wordunion All Select' B ', N' Book 'Union All Select' c ', N' Wrong 'Union All Select' D ', N' 鵽 'Union All Select' E ', N' 樲 'Union All Select 'F', n '鳆' union all succ 'g', n '腂' union all select 'h', n '夻' union all select 'j', n '攈' union all succ 'k', n '穒 'Union All Select' L ', N' 鱳 'Union All Select' M ', N' 旀 'Union All Select' N ', N' 桛 'Union All Select' O ', N' 沤 'Union All Select' P ', N' Exposure 'Union All Select' Q ', N' 囕 'Union All Select' R ', N' 鶸 'Union All Select' S ', N' 蜶 'Union All Select' T ', N' 箨'Union All Select' W ', N' 鹜 'Union All Select' X ', N' 鑂 'Union All Select' Y ', N' Rhyme 'Union All Select' Z ', N' 咗 ') T Where Word> @ Word Collate Chinese_PRC_CS_AS_KS_WS ORDER BY PY ASC) ELSE @Word End End @ Str = Right (@ Str, Len (@STR) -1) endreturn @ pyend - function call real Example: SELECT DBO.FUN_GETPY ('"The People' s Republic '), DBO.FUN_GETPY ('" People's Republic ') results are: zhrmghg
