sql server中字元集和排序規則到底什麼關系

--SQLSERVER 中的排序規則 伺服器》資料庫》表列

--------------------------------- 排序規則簡介 -------------------------------

-- 什麼叫排序規則呢？ 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

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

    )

ALTER TABLE tb ADD col3 VARCHAR (10 ) COLLATE Chinese_PRC_BIN

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

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'   --區分大小寫

-- 結果:@a<>@b

---------------------------SQL2000 适用 -----------------------------------------------------

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

--C:\Program   Files\Microsoft   SQL   Server\80\Tools\Binn\rebuildm.exe

-- 方法二sql   server   8.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

      name NVARCHAR ( 20) COLLATE Chinese_PRC_CI_AI_WS

INSERT   #t1

        SELECT   1 ,

                ' 中'

        UNION ALL

        SELECT   2 ,

                ' 國'

        SELECT   3 ,

                ' 人'

        SELECT   4 ,

                ' 阿'

INSERT   #t2

                ' 親'

                ' 好'

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

---------------------------------------------------------------------------------------

-- 例: 讓表 NAME列的内容按拼音排序：

    INSERT   #t1

SELECT   * FROM    #t1 ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS

/* 結果：

id name

----------- --------------------

4 阿

2 國

3 人

1 中

*/

-------------------------------------------------------------------------

-- 例：讓表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

4 一

2 乙

3 二

5 十

1 三

--------------------------- 計算漢字的筆劃 --------------------------------------------

-- 　　上面所有準備過程，隻是為了寫下面這個函數，這個函數撇開上面建的所有臨時表和固

-- 定表，為了通用和代碼轉移友善，把表 tab_hzbh 的内容寫在語句内，然後計算使用者輸入一串

-- 漢字的總筆劃：

USE [tempdb]

create function fun_getbh (@str nvarchar (4000 ))

returns int

as

begin

declare @word nchar ( 1), @n int

set @n= 0

while len ( @str)> 0

set @word=left( @str ,1 )

-- 如果非漢字，筆劃當計

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 ( '中華人民共和國 ' )

-- 簡繁體都行

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

-- 用得到筆劃總數相同的方法，我們也可以寫出求漢字拼音首字母的函數。如下：

create function fun_getPY (@str nvarchar (4000 ))

returns nvarchar ( 4000)

declare @word nchar ( 1), @PY nvarchar( 4000 )

set @PY= ''

-- 如果非漢字字元，傳回原字元

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 '咗 '

where word>= @word collate Chinese_PRC_CS_AS_KS_WS

order by PY ASC) else @word end )

return @PY

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 

本文轉自pizibaidu 51CTO部落格，原文連結：http://blog.51cto.com/pizibaidu/1627605，如需轉載請自行聯系原作者