SQL Server排序規則

吉普賽的歌發表於2017-01-10
SQLServer排序
--SQLSERVER中的排序規則 伺服器-》資料庫-》表列

--參考：http://www.jb51.net/article/889.htm

---------------------------------排序規則簡介--------------------------------
--什麼叫排序規則呢？MS是這樣描述的："在 Microsoft SQL Server 2000 中， 
--字串的物理儲存由排序規則控制。排序規則指定表示每個字元的位模式以及存 
--儲和比較字元所使用的規則。" 
--　　在查詢分析器內執行下面語句，可以得到SQL　SERVER支援的所有排序規則。 
--
--　　　　select * from ::fn_helpcollations() 
--
--排序規則名稱由兩部份構成，前半部份是指本排序規則所支援的字符集。 
--如： 
--　　Chinese_PRC_CS_AI_WS 
--前半部份：指UNICODE字符集，Chinese_PRC_指標對大陸簡體字UNICODE的排序規則。 
--排序規則的後半部份即字尾 含義： 
--　　_BIN 二進位制排序 
--　　_CI(CS) 是否區分大小寫，CI不區分，CS區分 
--　　_AI(AS) 是否區分重音，AI不區分，AS區分　　　 
--　　_KI(KS) 是否區分假名型別,KI不區分，KS區分　 
--_WI(WS) 是否區分寬度 WI不區分，WS區分　 
--
--區分大小寫:如果想讓比較將大寫字母和小寫字母視為不等，請選擇該選項。 
--區分重音:如果想讓比較將重音和非重音字母視為不等，請選擇該選項。如果選擇該選項， 
--比較還將重音不同的字母視為不等。 
--區分假名:如果想讓比較將片假名和平假名日語音節視為不等，請選擇該選項。 
--區分寬度:如果想讓比較將半形字元和全形字元視為不等，請選擇該選項 
-------------------------------------------------------------------------------------



sp_helpsort 
SELECT SERVERPROPERTY ('Collation') 

--------------------------------------------
--2.更改伺服器排序規則 
--更改 SQL Server 2005 例項的預設排序規則的操作可能會比較複雜，包括以下步驟： 
 
--確保具有重新建立使用者資料庫及這些資料庫中的所有物件所需的全部資訊或指令碼。 
--使用工具（例如大容量複製）匯出所有資料。 
--刪除所有使用者資料庫。 
--重新生成在 setup 命令的 SQLCOLLATION 屬性中指定新的排序規則的 master 資料庫。例如： 
--複製程式碼 
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI 
--有關重新生成 master 資料庫的詳細資訊，請參閱如何重新生成 SQL Server 2005 的 Master 資料庫。 
--建立所有資料庫及這些資料庫中的所有物件。 
--匯入所有資料。 
--注意：  
--可以為建立的每個新資料庫指定預設排序規則，而不更改 SQL Server 2005 例項的預設排序規則。


-----------------------------------------------------------------------------------
--3.設定和更改資料庫排序規則 
--建立新資料庫時，可以使用下列內容之一指定排序規則： 
--CREATE DATABASE 語句的 COLLATE 子句。 
--SQL Server Management Studio. 
--SQL 管理物件 (SMO) 中的 Database.Collation 屬性。 
--如果未指定排序規則，則使用伺服器排序規則。 
--可以使用 ALTER DATABASE 語句的 COLLATE 子句來更改在使用者資料庫中建立的任何新物件的排序規則。使用此語句不能更改任何現有使用者定義的表中列的排序規則。使用 ALTER TABLE 的 COLLATE 子句可以更改這些列的排序規則。 
--更改資料庫排序規則時，需要更改下列內容： 
--資料庫的預設排序規則，這一新的預設排序規則將應用於資料庫中後續建立的所有列、使用者定義的資料型別、變數和引數。根據資料庫中定義的物件解析 SQL 語句中指定的物件識別符號時，也使用新的預設排序規則。 
--將系統表中的任何 char、varchar、text、nchar、nvarchar 或 ntext 列更改為使用新的排序規則。 
--將儲存過程和使用者定義函式的所有現有 char、varchar、text、nchar、nvarchar 或 ntext 引數和標量返回值更改為使用新的排序規則。 
--將 char、varchar、text、nchar、nvarchar 或 ntext 系統資料型別和基於這些系統資料型別的所有使用者定義的資料型別更改為使用新的預設排序規則。 
 
--SQL code :
--------------------------資料庫----------------------------------------
--1.將資料庫的字符集修改為：
ALTER DATABASE [pratice] COLLATE Chinese_PRC_CI_AS
 
--2.為資料庫指定排序規則
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GO
 
ALTER DATABASE db COLLATE Chinese_PRC_BIN
GO


ALTER   DATABASE [pratice]  COLLATE   Chinese_PRC_CS_AS  --區分大小寫
 
ALTER   DATABASE   [pratice] COLLATE  Chinese_PRC_CI_AS --不區分大小寫
---------------------------表中的列---------------------------------------
--為表中的列指定排序規則
CREATE TABLE tb
    (
      col1 VARCHAR(10) ,
      col2 VARCHAR(10) COLLATE Chinese_PRC_CI_AS
    )
GO

ALTER TABLE tb ADD col3 VARCHAR(10) COLLATE Chinese_PRC_BIN
GO

ALTER TABLE tb ALTER COLUMN col2 VARCHAR(10) COLLATE Latin1_General_CS_AS_KS_WS
GO


ALTER   TABLE   tb  ALTER   COLUMN   colname   NVARCHAR(100)   COLLATE   Chinese_PRC_CI_AS  --不區分大小寫

ALTER   TABLE   tb  ALTER   COLUMN   colname   NVARCHAR(100)   COLLATE   Chinese_PRC_CS_AS  --區分大小寫

---------------------------------------------------------------------
--3. 為字元變數和引數應用排序規則
DECLARE @a VARCHAR(10) ,
    @b VARCHAR(10)
SELECT  @a = 'a' ,
        @b = 'A'
 
--使用排序規則 Chinese_PRC_CI_AS
SELECT  CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' --Chinese_PRC_CI_AS不區分大小寫
             ELSE '@a<>@b'
        END
--結果:@a=@b
 
--使用排序規則 Chinese_PRC_BIN
SELECT  CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b'
             ELSE '@a<>@b'
        END
--結果:@a<>@b

---------------------------SQL2000適用------------------------------------------------------
--方法一.安裝SQL2000時選擇區分大小寫或安裝完以後重建mastar，選擇區分大小

--C:\Program   Files\Microsoft   SQL   Server\80\Tools\Binn\rebuildm.exe
 
--方法二.sql   server   8.0以上的版本才可以，7.0及其以下不支援
ALTER   DATABASE  [pratice]   COLLATE   Chinese_PRC_CS_AS

--修改排序規則，改成大小寫敏感的排序規則
--如果只修改一個表，用alter   table語句  
--如果修改一個庫的預設排序規則，用alter   datebase語句  
--如果修改整個伺服器的預設排序規則，用Rebuildm.exe重建master庫  
--指定排序規則就可以了


-------------------------------------------------------------------
--注意需要使用NVARCHAR()資料型別!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--因為排序規則而出錯的例子
--USE [tempdb]
--GO
--DROP TABLE #t1
--DROP TABLE #t2
CREATE TABLE #t1
    (
      value INT ,
      name NVARCHAR(20) COLLATE Albanian_CI_AI_WS
    ) 

CREATE TABLE #t2
    (
      value INT ,
      name NVARCHAR(20) COLLATE Chinese_PRC_CI_AI_WS
    )

INSERT  #t1
        SELECT  1 ,
                '中'
        UNION ALL
        SELECT  2 ,
                '國'
        UNION ALL
        SELECT  3 ,
                '人'
        UNION ALL
        SELECT  4 ,
                '阿' 

INSERT  #t2
        SELECT  1 ,
                '親'
        UNION ALL
        SELECT  2 ,
                '國'
        UNION ALL
        SELECT  3 ,
                '好'
        UNION ALL
        SELECT  4 ,
                '阿' 

SELECT  * FROM    #t1 A INNER JOIN #t2 B ON A.name = B.name 

--解決方法
SELECT  * FROM    #t1 A INNER JOIN #t2 B ON A.name = B.name COLLATE Chinese_PRC_CI_AI_WS 

---------------------------------------------------------------------------------------
--例1:讓表NAME列的內容按拼音排序： 
CREATE TABLE #t1
    (
      value INT ,
      name NVARCHAR(20) COLLATE Albanian_CI_AI_WS
    ) 
    
    INSERT  #t1
        SELECT  1 ,
                '中'
        UNION ALL
        SELECT  2 ,
                '國'
        UNION ALL
        SELECT  3 ,
                '人'
        UNION ALL
        SELECT  4 ,
                '阿' 

SELECT  * FROM    #t1 ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS 

/*結果： 
id name 
----------- -------------------- 
4 阿 
2 國 
3 人 
1 中 
*/ 

-------------------------------------------------------------------------
--例2：讓表NAME列的內容按姓氏筆劃排序： 

create table #t(id int,name NVARCHAR(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 三 
*/ 

---------------------------計算漢字的筆劃---------------------------------------------
--　　上面所有準備過程，只是為了寫下面這個函式，這個函式撇開上面建的所有臨時表和固 
--定表，為了通用和程式碼轉移方便，把表tab_hzbh的內容寫在語句內，然後計算使用者輸入一串 
--漢字的總筆劃： 
USE [tempdb]
GO
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'風' 
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


select dbo.fun_getbh('中華'),dbo.fun_getbh('中華人民共和國') 
--簡繁體都行


-------------------------------------------------------------------------
--用排序規則的特性得到漢字拼音首字母 

--用得到筆劃總數相同的方法，我們也可以寫出求漢字拼音首字母的函式。如下： 
USE [tempdb]
GO
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 

select dbo.fun_getPY('中華'),dbo.fun_getPY('中華人民共和國') 
--結果都為：ZHRMGHG 

-------------------------------------------------------------------------
--先用SQLSERVER方法得到所有漢字，不用字典，我們簡單利用SQL語句就可以得到： 
select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b 

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

--然後，我們用Select語句，讓它按筆劃排序。 

select code,nchar(code) as CNWord 
from #t 
order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code

相關文章