來(lái)源:億思維 日期:2007-3-1 20:30:50 閱讀:265 評論:1
一、排序規則簡(jiǎn)介：
什么叫排序規則呢？MS是這樣描述的："在 Microsoft SQL Server 中，
字符串的物理存儲由排序規則控制。排序規則指定表示每個(gè)字符的位模式以及存
儲和比較字符所使用的規則。"
在查詢(xún)分析器內執行下面語(yǔ)句，可以得到SQL SERVER支持的所有排序規則。
select * from ::fn_helpcollations()
排序規則名稱(chēng)由兩部份構成，前半部份是指本排序規則所支持的字符集。
如：
Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指針對大陸簡(jiǎn)體字UNICODE的排序規則，按拼音排序。
Chinese_PRC_Stroke 表示按漢字筆畫(huà)排序；
排序規則的后半部份即后綴 含義：
_BIN 二進(jìn)制排序
_CI(CS) 是否區分大小寫(xiě)，CI不區分，CS區分(case-insensitive/case-sensitive)
_AI(AS) 是否區分重音，AI不區分，AS區分(accent-insensitive/accent-sensitive)
_KI(KS) 是否區分假名類(lèi)型,KI不區分，KS區分(kanatype-insensitive/kanatype-sensitive)
_WI(WS) 是否區分寬度 WI不區分，WS區分(width-insensitive/width-sensitive)
區分大小寫(xiě):如果想讓比較將大寫(xiě)字母和小寫(xiě)字母視為不等，請選擇該選項。
區分重音:如果想讓比較將重音和非重音字母視為不等，請選擇該選項。如果選擇該選項，
比較還將重音不同的字母視為不等。
區分假名:如果想讓比較將片假名和平假名日語(yǔ)音節視為不等，請選擇該選項。
區分寬度:如果想讓比較將半角字符和全角字符視為不等，請選擇該選項。
二、排序規則選擇：
如果 SQL Server 實(shí)例的所有用戶(hù)都使用同一種語(yǔ)言，則應選取支持該語(yǔ)言的排序規則。例如，如果所有用戶(hù)都講法語(yǔ)，則選擇法語(yǔ)排序規則。如果您的 SQL Server 實(shí)例的用戶(hù)講多種語(yǔ)言，則應選擇能最好地滿(mǎn)足各種語(yǔ)言需要的排序規則。例如，如果用戶(hù)一般都講西歐語(yǔ)言，則選擇 Latin1_General 排序規則。
如果要支持講多種語(yǔ)言的用戶(hù)，則對于所有字符數據使用 Unicode 數據類(lèi)型 nchar、nvarchar 和 nvarchar(max) 是非常重要的。Unicode 可避免非 Unicode 的 char、varchar 和 text 數據類(lèi)型帶來(lái)的代碼頁(yè)轉換難題。因為排序規則定義用于比較操作的排序次序和 Unicode 字符的排序，所以當用 Unicode 數據類(lèi)型實(shí)現所有列時(shí)，排序規則仍會(huì )產(chǎn)生不同。即使使用 Unicode 數據類(lèi)型存儲字符數據時(shí)，也應選擇支持大多數用戶(hù)的排序規則，以防使用非 Unicode 數據類(lèi)型實(shí)現列或變量。
SQL Server 只支持由基礎操作系統支持的代碼頁(yè)。在執行取決于排序規則的操作時(shí)，引用的對象所使用的 SQL Server 排序規則必須使用計算機上運行的操作系統所支持的代碼頁(yè)。
如果指定的排序規則（或引用的對象所使用的排序規則）使用 Windows 操作系統不支持的代碼頁(yè)，則 SQL Server 將發(fā)出錯誤。對此錯誤的響應取決于計算機上安裝的 Windows 操作系統的版本。Windows 2000 及更新版本支持由 SQL Server 排序規則使用的所有代碼頁(yè)。因此，不會(huì )出現該錯誤消息。
三、排序規則的語(yǔ)法：
Windows 排序規則名稱(chēng)由排序規則指示器和比較風(fēng)格構成。
語(yǔ)法
< Windows_collation_name > :: =
CollationDesignator_<ComparisonStyle>
< ComparisonStyle > ::=
CaseSensitivity_AccentSensitivity
[_KanatypeSensitive [_WidthSensitive ] ]
| _BIN
參數
CollationDesignator
指定 Windows 排序規則使用的基本排序規則?；九判蛞巹t包括：
當指定按字典排序時(shí)應用其排序規則的字母表或語(yǔ)言
用于存儲非 Unicode 字符數據的代碼頁(yè)。 例如 Latin1_General 或法文，兩者都使用代碼頁(yè) 1252，或土耳其文，它使用代碼頁(yè) 1254。
CaseSensitivity
CI 指定不區分大小寫(xiě)，CS 指定區分大小寫(xiě)。
AccentSensitivity
AI 指定不區分重音，AS 指定區分重音。
KanatypeSensitive
Omitted 指定不區分大小寫(xiě)，KS 指定區分假名類(lèi)型。
WidthSensitivity
Omitted 指定不區分大小寫(xiě)，WS 指定區分大小寫(xiě)。
BIN
指定使用二進(jìn)制排序次序。
注釋
Microsoft® SQL Server™ 2000 Windows 排序規則的指示器為：
SQL Server 2000 排序規則指示器 用于非 Unicode 數據的代碼頁(yè)
支持的 Windows 區域設置
Albanian 1250 阿爾巴尼亞語(yǔ)
Arabic 1256 阿拉伯語(yǔ)（阿爾及利亞）、阿拉伯語(yǔ)（巴林）、阿拉伯語(yǔ)（埃及）、阿拉伯語(yǔ)（伊朗）、阿拉伯語(yǔ)（約旦）、阿拉伯語(yǔ)（科威特）、阿拉伯語(yǔ)（黎巴嫩）、阿拉伯語(yǔ)（利比亞）、阿拉伯語(yǔ)（摩洛哥）、阿拉伯語(yǔ)（阿曼）、阿拉伯語(yǔ)（卡塔爾）、阿拉伯語(yǔ)（沙特阿拉伯）、阿拉伯語(yǔ)（敘利亞）、阿拉伯語(yǔ)（突尼斯）、阿拉伯語(yǔ)（阿拉伯聯(lián)合酋長(cháng)國）、阿拉伯語(yǔ)（也門(mén)）、波斯語(yǔ)、烏爾都語(yǔ)
Chinese_PRC 936 中文（香港特別行政區），中文（中華人民共和國），中文（新加坡）
Chinese_PRC_Stroke 936 按漢字筆畫(huà)排序（中華人民共和國）
Chinese_Taiwan_Bopomofo 950 按漢語(yǔ)拼音排序（臺灣）
Chinese_Taiwan_Stroke 950 繁體中文（臺灣）
Croatian 1250 克羅地亞語(yǔ)
Cyrillic_General 1251 保加利亞語(yǔ)、白俄羅斯語(yǔ)、俄羅斯語(yǔ)、塞爾維亞語(yǔ)
Czech 1250 捷克語(yǔ)
Danish_Norwegian 1252 丹麥語(yǔ)、挪威語(yǔ) (Bokmål)、挪威語(yǔ)（Nyorsk）
Estonian 1257 愛(ài)沙尼亞語(yǔ)
Finnish_Swedish 1252 芬蘭語(yǔ)、瑞典語(yǔ)
French 1252 法語(yǔ)（比利時(shí)）、法語(yǔ)（加拿大）、法語(yǔ)（盧森堡）、法語(yǔ)（標準）、法語(yǔ)（瑞士）
Georgian_Modern_Sort 1252 按現代格魯吉亞語(yǔ)排序
German_PhoneBook 1252 按德語(yǔ)電話(huà)號碼簿排序
Greek 1253 希臘語(yǔ)
Hebrew 1255 希伯來(lái)語(yǔ)
Hindi 只用于 Unicode 數據類(lèi)型 北印度語(yǔ)
Hungarian 1250 匈牙利語(yǔ)
Hungarian_Technical 1250
Icelandic 1252 冰島語(yǔ)
Japanese 932 日語(yǔ)
Japanese_Unicode 932
Korean_Wansung 949 朝鮮語(yǔ)
Korean_Wansung_Unicode 949
Latin1_General 1252 南非荷蘭語(yǔ)、巴斯克語(yǔ)、加泰羅尼亞語(yǔ)、荷蘭語(yǔ)（比利時(shí)）、荷蘭語(yǔ)（標準）、英語(yǔ)（澳大利亞）、英語(yǔ)（不列顛）、英語(yǔ)（加拿大）、英語(yǔ)（加勒比）、英語(yǔ)（愛(ài)爾蘭）、英語(yǔ)（牙買(mǎi)加）、英語(yǔ)（新西蘭）、英語(yǔ)（南非）、英語(yǔ)（美國）、法羅語(yǔ)、德語(yǔ)（奧地利）、德語(yǔ)（列支敦士登）、德語(yǔ)（盧森堡）、德語(yǔ)（標準）、德語(yǔ)（瑞士）、印度尼西亞語(yǔ)、意大利語(yǔ)、意大利語(yǔ)（瑞士）、葡萄牙語(yǔ)（巴西）、葡萄牙語(yǔ)（標準）
Latvian 1257 拉脫維亞語(yǔ)
Lithuanian 1257 立陶宛語(yǔ)
Lithuanian_Classic 1257
Macedonian 1251 馬其頓語(yǔ)
Mexican_Trad_Spanish 1252 西班牙語(yǔ)（墨西哥）、西班牙語(yǔ)（傳統排序）
Modern_Spanish 1252 西班牙語(yǔ)（阿根廷）、西班牙語(yǔ)（玻利維亞）、西班牙語(yǔ)（智利）、西班牙語(yǔ)（哥倫比亞）、西班牙語(yǔ)（哥斯達黎加）、西班牙語(yǔ)（多米尼加共和國）、西班牙語(yǔ)（厄瓜多爾）、西班牙語(yǔ)（危地馬拉）、西班牙語(yǔ)（現代排序）、西班牙語(yǔ)（巴拿馬）、西班牙語(yǔ)（巴拉圭）、西班牙語(yǔ)（秘魯）、西班牙語(yǔ)（烏拉圭）、西班牙語(yǔ)（委內瑞拉）
Polish 1250 波蘭語(yǔ)
Romanian 1250 羅馬尼亞語(yǔ)
Slovak 1250 斯洛伐克語(yǔ)
Slovenian 1250 斯洛文尼亞語(yǔ)
Thai 874 泰國語(yǔ)
Turkish 1254 土耳其語(yǔ)
Ukrainian 1251 烏克蘭語(yǔ)
Vietnamese 1258 越南語(yǔ)
示例
下面是 Windows 排序規則名稱(chēng)的一些示例：
Latin1_General_CI_AS 排序規則使用 Latin1 General 字典排序規則，代碼頁(yè)為 1252。不區分大小寫(xiě)但區分重音。
Estonian_CS_AS 排序規則使用愛(ài)沙尼亞字典排序規則，代碼頁(yè)為 1257。區分大小寫(xiě)并區分重音。
Latin1_General_BIN 排序規則使用代碼頁(yè) 1252 和二進(jìn)制排序規則。忽略 Latin1 General 字典排序規則。
四、修改、查看排序規則：
------修改列的排序規則
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS
------修改數據庫的排序規則
ALTER DATABASE database
COLLATE Chinese_PRC_CS_AS
------查看某個(gè)表的排序規則
select collation from syscolumns
where id=object_id(N'yourtablename')
五、排序規則應用：
SQL SERVER提供了大量的WINDOWS和SQLSERVER專(zhuān)用的排序規則，但它的應用往往
被開(kāi)發(fā)人員所忽略。其實(shí)它在實(shí)踐中大有用處。
例1:讓表NAME列的內容按拼音排序：
create table #t(id int,name varchar(20))
insert #t select 1,'中'
union all select 2,'國'
union all select 3,'人'
union all select 4,'阿'
select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS
drop table #t
/*結果：
id name
----------- --------------------
4 阿
2 國
3 人
1 中
*/
例2：讓表NAME列的內容按姓氏筆劃排序：
create table #t(id int,name varchar(20))
insert #t select 1,'三'
union all select 2,'乙'
union all select 3,'二'
union all select 4,'一'
union all select 5,'十'
select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS
drop table #t
/*結果：
id name
----------- --------------------
4 一
2 乙
3 二
5 十
1 三
*/
排序規則應用擴展：
SQL SERVER漢字排序規則可以按拼音、筆劃等排序，那么我們如何利用這種功能
來(lái)處理漢字的一些難題呢？我現在舉個(gè)例子：
用排序規則的特性計算漢字筆劃
要計算漢字筆劃，我們得先做準備工作，我們知道，WINDOWS多國漢字，UNICODE目前
收錄漢字共20902個(gè)。簡(jiǎn)體GBK碼漢字UNICODE值從19968開(kāi)始。
首先，我們先用SQLSERVER方法得到所有漢字，不用字典，我們簡(jiǎn)單利用SQL語(yǔ)句就
可以得到：
select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b
再用以下語(yǔ)句，我們就得到所有漢字，它是按UNICODE值排序的：
select code,nchar(code) as CNWord from #t
然后，我們用SELECT語(yǔ)句，讓它按筆劃排序。
select code,nchar(code) as CNWord
from #t
order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code
結果：
code CNWord
----------- ------
19968 一
20008 丨
20022 丶
20031 丿
20032 乀
20033 乁
20057 乙
20058 乚
20059 乛
20101 亅
19969 丁
..........
從上面的結果，我們可以清楚的看到，一筆的漢字，code是從19968到20101，從小到大排，但到
了二筆漢字的第一個(gè)字“丁”，CODE為19969，就不按順序而重新開(kāi)始了。有了這結果，我們就可以輕
松的用SQL語(yǔ)句得到每種筆劃漢字歸類(lèi)的第一個(gè)或最后一個(gè)漢字。
下面用語(yǔ)句得到最后一個(gè)漢字：
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 A
left join #t1 B on A.id=B.id-1 and A.code<B.code
where B.code is null
order by A.id
得到36個(gè)漢字，每個(gè)漢字都是每種筆劃數按Chinese_PRC_Stroke_CS_AS_KS_WS排序規則排序后的
最后一個(gè)漢字：
亅阝馬風(fēng)龍齊龜齒鴆齔龕龂齠齦齪龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘
上面可以看出：“亅”是所有一筆漢字排序后的最后一個(gè)字，“阝”是所有二筆漢字排序后的最后
一個(gè)字......等等。
但同時(shí)也發(fā)現，從第33個(gè)漢字“龗(33筆)”后面的筆劃有些亂，不正確。但沒(méi)關(guān)系，比“龗”筆劃
多的只有四個(gè)漢字，我們手工加上：齾35筆，齉36筆，靐39筆，龘64筆
建漢字筆劃表（TAB_HZBH）：
create table tab_hzbh(id int identity,cnword nchar(1))
--先插入前33個(gè)漢字
insert tab_hzbh
select top 33 A.cnword
from #t1 A
left join #t1 B on A.id=B.id-1 and A.code<B.code
where B.code is null
order by A.id
--再加最后四個(gè)漢字
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
到此為止，我們可以得到結果了，比如我們想得到漢字“國”的筆劃：
declare @a nchar(1)
set @a='國'
select top 1 id
from tab_hzbh
where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS
order by id
id
-----------
8
(結果：漢字“國”筆劃數為8)
上面所有準備過(guò)程，只是為了寫(xiě)下面這個(gè)函數，這個(gè)函數撇開(kāi)上面建的所有臨時(shí)表和固
定表，為了通用和代碼轉移方便，把表tab_hzbh的內容寫(xiě)在語(yǔ)句內，然后計算用戶(hù)輸入一串
漢字的總筆劃：
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)
--如果非漢字，筆劃當0計
set @n=@n+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 id from (
select 1 as id,N'亅' as word
union all select 2,N'阝'
union all select 3,N'馬'
union all select 4,N'風(fēng)'
union all select 5,N'龍'
union all select 6,N'齊'
union all select 7,N'龜'
union all select 8,N'齒'
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'齦'
union all select 15,N'齪'
union all select 16,N'龍'
union all select 17,N'龠'
union all select 18,N'龎'
union all select 19,N'龐'
union all select 20,N'龑'
union all select 21,N'龡'
union all select 22,N'龢'
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'鸞'
union all select 31,N'麣'
union all select 32,N'龖'
union all select 33,N'龗'
union all select 35,N'齾'
union all select 36,N'齉'
union all select 39,N'靐'
union all select 64,N'龘'
) 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
--函數調用實(shí)例：
select dbo.fun_getbh('中華人民共和國'),dbo.fun_getbh('中華人民共和國')
執行結果：筆劃總數分別為39和46，簡(jiǎn)繁體都行。
當然，你也可以把上面“UNION ALL”內的漢字和筆劃改存在固定表內，在漢字
列建CLUSTERED INDEX，列排序規則設定為：
Chinese_PRC_Stroke_CS_AS_KS_WS
這樣速度更快。如果你用的是BIG5碼的操作系統，你得另外生成漢字，方法一樣。
但有一點(diǎn)要記?。哼@些漢字是通過(guò)SQL語(yǔ)句SELECT出來(lái)的，不是手工輸入的，更不
是查字典得來(lái)的，因為新華字典畢竟不同于UNICODE字符集，查字典的結果會(huì )不正
確。
用排序規則的特性得到漢字拼音首字母
用得到筆劃總數相同的方法，我們也可以寫(xiě)出求漢字拼音首字母的函數。如下：
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)
--如果非漢字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select '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'曝'
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'韻'
union all select 'Z',N'咗'
) 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
--函數調用實(shí)例：
select dbo.fun_getPY('中華人民共和國'),dbo.fun_getPY('中華人民共和國')
結果都為：ZHRMGHG
六、常見(jiàn)問(wèn)題處理：
1.“無(wú)法解決 equal to 操作的排序規則沖突。”
示例1：
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 )
表建好后，執行連接查詢(xún)：
select * from #t1 A inner join #t2 B on A.name=B.name
這樣，錯誤就出現了：
服務(wù)器: 消息 446，級別 16，狀態(tài) 9，行 1
無(wú)法解決 equal to 操作的排序規則沖突。
要排除這個(gè)錯誤，最簡(jiǎn)單方法是，表連接時(shí)指定它的排序規則，這樣錯誤就
不再出現了。語(yǔ)句這樣寫(xiě)：
select *
from #t1 A inner join #t2 B
on A.name=B.name collate Chinese_PRC_CI_AI_WS
示例2：
例如，在創(chuàng )建表時(shí)考慮使用下面的 Transact-SQL 語(yǔ)句：
CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) COLLATE greek_ci_as,
LatinCol nvarchar(10) COLLATE latin1_general_cs_as
)
INSERT TestTab VALUES (1, N'A', N'a')
GO
該語(yǔ)句創(chuàng )建了一個(gè)包含以下兩列的表：一列使用不區分大小寫(xiě)和區分重音的希臘語(yǔ)排序規則，而另一列使用區分大小寫(xiě)和重音的通用 Latin1 排序規則。
您可以嘗試使用查詢(xún)來(lái)顯式比較這兩列：
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol
但是，該查詢(xún)會(huì )返回一個(gè)錯誤：
Msg 446, Level 16, State 9, Server V-MICHKA3, Line 1
無(wú)法解決等于運算的排序規則沖突。
之所以會(huì )出現此錯誤，是因為服務(wù)器無(wú)法使用不同的排序規則來(lái)比較兩段文本。但是，如果您使用 COLLATE 關(guān)鍵字顯式創(chuàng )建一個(gè)允許這兩列兼容的表達式，則查詢(xún)將以如下方式執行：
SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as
還需注意的是，盡管 LatinCol 通常有一個(gè)區分大小寫(xiě)的排序規則，但表達式不區分大小寫(xiě)的排序規則會(huì )將其覆蓋，從而使“A”的大寫(xiě)和小寫(xiě)被視為等同。
