1 --SQLSERVER 中的排序规则 服务器》数据库》表列
2
3 -- 参考：http://www.jb51.net/article/889.htm
4
5 --------------------------------- 排序规则简介 -------------------------------
6 -- 什么叫排序规则呢？ MS是这样描述的： " 在Microsoft SQL Server 2000 中，
7 -- 字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存
8 -- 储和比较字符所使用的规则。 "
9 -- 在查询分析器内执行下面语句，可以得到 SQL SERVER 支持的所有排序规则。
10 --
11 -- select * from ::fn_helpcollations()
12 --
13 -- 排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
14 -- 如：
15 -- Chinese_PRC_CS_AI_WS
16 -- 前半部份：指 UNICODE字符集， Chinese_PRC_ 指针对大陆简体字 UNICODE的排序规则。
17 -- 排序规则的后半部份即后缀含义：
18 -- _BIN 二进制排序
19 -- _CI(CS) 是否区分大小写， CI 不区分，CS 区分
20 -- _AI(AS) 是否区分重音， AI 不区分，AS 区分
21 -- _KI(KS) 是否区分假名类型 ,KI 不区分，KS 区分
22 --_WI(WS) 是否区分宽度 WI不区分， WS 区分
23 --
24 -- 区分大小写 :如果想让比较将大写字母和小写字母视为不等，请选择该选项。
25 -- 区分重音: 如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，
26 -- 比较还将重音不同的字母视为不等。
27 -- 区分假名: 如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
28 -- 区分宽度: 如果想让比较将半角字符和全角字符视为不等，请选择该选项
29 -------------------------------------------------------------------------------------
30
31
32
33 sp_helpsort
34 SELECT SERVERPROPERTY ('Collation' )
35
36 --------------------------------------------
37 --2. 更改服务器排序规则
38 -- 更改SQL Server 2005 实例的默认排序规则的操作可能会比较复杂，包括以下步骤：
39
40 -- 确保具有重新创建用户数据库及这些数据库中的所有对象所需的全部信息或脚本。
41 -- 使用工具（例如大容量复制）导出所有数据。
42 -- 删除所有用户数据库。
43 -- 重新生成在 setup 命令的 SQLCOLLATION 属性中指定新的排序规则的 master 数据库。例如：
44 -- 复制代码
45 start /wait setup . exe / qb INSTANCENAME =MSSQLSERVER REINSTALL = SQL_Engine REBUILDDATABASE= 1 SAPWD= test SQLCOLLATION =SQL_Latin1_General_CP1_CI_AI
46 -- 有关重新生成 master 数据库的详细信息，请参阅如何重新生成 SQL Server 2005 的Master 数据库。
47 -- 创建所有数据库及这些数据库中的所有对象。
48 -- 导入所有数据。
49 -- 注意：
50 -- 可以为创建的每个新数据库指定默认排序规则，而不更改 SQL Server 2005 实例的默认排序规则。
51
52
53 -----------------------------------------------------------------------------------
54 --3. 设置和更改数据库排序规则
55 -- 创建新数据库时，可以使用下列内容之一指定排序规则：
56 --CREATE DATABASE 语句的COLLATE 子句。
57 --SQL Server Management Studio.
58 --SQL 管理对象(SMO) 中的 Database.Collation 属性。
59 -- 如果未指定排序规则，则使用服务器排序规则。
60 -- 可以使用ALTER DATABASE 语句的 COLLATE 子句来更改在用户数据库中创建的任何新对象的排序规则。使用此语句不能更改任何现有用户定义的表中列的排序规则。使用 ALTER TABLE 的COLLATE 子句可以更改这些列的排序规则。
61 -- 更改数据库排序规则时，需要更改下列内容：
62 -- 数据库的默认排序规则，这一新的默认排序规则将应用于数据库中后续创建的所有列、用户定义的数据类型、变量和参数。根据数据库中定义的对象解析 SQL 语句中指定的对象标识符时，也使用新的默认排序规则。
63 -- 将系统表中的任何 char、 varchar 、text 、 nchar、 nvarchar 或ntext 列更改为使用新的排序规则。
64 -- 将存储过程和用户定义函数的所有现有 char 、varchar 、 text、 nchar 、nvarchar 或 ntext 参数和标量返回值更改为使用新的排序规则。
65 -- 将char 、 varchar、 text 、nchar 、 nvarchar 或 ntext 系统数据类型和基于这些系统数据类型的所有用户定义的数据类型更改为使用新的默认排序规则。
66
67 --SQL code :
68 -------------------------- 数据库 ---------------------------------------
69 --1. 将数据库的字符集修改为：
70 ALTER DATABASE [pratice] COLLATE Chinese_PRC_CI_AS
71
72 --2. 为数据库指定排序规则
73 CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
74 GO
75
76 ALTER DATABASE db COLLATE Chinese_PRC_BIN
77 GO
78
79
80 ALTER DATABASE [pratice] COLLATE Chinese_PRC_CS_AS -- 区分大小写
81
82 ALTER DATABASE [pratice] COLLATE Chinese_PRC_CI_AS -- 不区分大小写
83 --------------------------- 表中的列 --------------------------------------
84 -- 为表中的列指定排序规则
85 CREATE TABLE tb
86 (
87 col1 VARCHAR (10 ) ,
88 col2 VARCHAR (10 ) COLLATE Chinese_PRC_CI_AS
89 )
90 GO
91
92 ALTER TABLE tb ADD col3 VARCHAR (10 ) COLLATE Chinese_PRC_BIN
93 GO
94
95 ALTER TABLE tb ALTER COLUMN col2 VARCHAR ( 10) COLLATE Latin1_General_CS_AS_KS_WS
96 GO
97
98
99 ALTER TABLE tb ALTER COLUMN colname NVARCHAR ( 100) COLLATE Chinese_PRC_CI_AS -- 不区分大小写
100
101 ALTER TABLE tb ALTER COLUMN colname NVARCHAR ( 100) COLLATE Chinese_PRC_CS_AS -- 区分大小写
102
103 ---------------------------------------------------------------------
104 --3. 为字符变量和参数应用排序规则
105 DECLARE @a VARCHAR ( 10) ,
106 @b VARCHAR (10 )
107 SELECT @a = 'a' ,
108 @b = 'A'
109
110 -- 使用排序规则 Chinese_PRC_CI_AS
111 SELECT CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' --Chinese_PRC_CI_AS 不区分大小写
112 ELSE '@a<>@b'
113 END
114 -- 结果:@a=@b
115
116 -- 使用排序规则 Chinese_PRC_BIN
117 SELECT CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b' --区分大小写
118 ELSE '@a<>@b'
119 END
120 -- 结果:@a<>@b
121
122 ---------------------------SQL2000 适用 -----------------------------------------------------
123 -- 方法一安装 SQL2000时选择区分大小写或安装完以后重建 mastar ，选择区分大小
124
125 --C:\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe
126
127 -- 方法二sql server 8.0 以上的版本才可以，及其以下不支持
128 ALTER DATABASE [pratice] COLLATE Chinese_PRC_CS_AS
129
130 -- 修改排序规则，改成大小写敏感的排序规则
131 -- 如果只修改一个表，用 alter table语句
132 -- 如果修改一个库的默认排序规则，用 alter datebase 语句
133 -- 如果修改整个服务器的默认排序规则，用 Rebuildm.exe 重建master 库
134 -- 指定排序规则就可以了
135
136
137 -------------------------------------------------------------------
138 -- 注意需要使用 NVARCHAR()数据类型 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
139 -- 因为排序规则而出错的例子
140 --USE [tempdb]
141 --GO
142 --DROP TABLE #t1
143 --DROP TABLE #t2
144 CREATE TABLE #t1
145 (
146 value INT ,
147 name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS
148 )
149
150 CREATE TABLE #t2
151 (
152 value INT ,
153 name NVARCHAR ( 20) COLLATE Chinese_PRC_CI_AI_WS
154 )
155
156 INSERT #t1
157 SELECT 1 ,
158 ' 中'
159 UNION ALL
160 SELECT 2 ,
161 ' 国'
162 UNION ALL
163 SELECT 3 ,
164 ' 人'
165 UNION ALL
166 SELECT 4 ,
167 ' 阿'
168
169 INSERT #t2
170 SELECT 1 ,
171 ' 亲'
172 UNION ALL
173 SELECT 2 ,
174 ' 国'
175 UNION ALL
176 SELECT 3 ,
177 ' 好'
178 UNION ALL
179 SELECT 4 ,
180 ' 阿'
181
182 SELECT * FROM #t1 A INNER JOIN #t2 B ON A . name = B .name
183
184 -- 解决方法
185 SELECT * FROM #t1 A INNER JOIN #t2 B ON A . name = B .name COLLATE Chinese_PRC_CI_AI_WS
186
187 ---------------------------------------------------------------------------------------
188 -- 例: 让表 NAME列的内容按拼音排序：
189 CREATE TABLE #t1
190 (
191 value INT ,
192 name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS
193 )
194
195 INSERT #t1
196 SELECT 1 ,
197 ' 中'
198 UNION ALL
199 SELECT 2 ,
200 ' 国'
201 UNION ALL
202 SELECT 3 ,
203 ' 人'
204 UNION ALL
205 SELECT 4 ,
206 ' 阿'
207
208 SELECT * FROM #t1 ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS
209
210 /* 结果：
211 id name
212 ----------- --------------------
213 4 阿
214 2 国
215 3 人
216 1 中
217 */
218
219 -------------------------------------------------------------------------
220 -- 例：让表NAME 列的内容按姓氏笔划排序：
221
222 create table #t (id int ,name NVARCHAR( 20 ))
223
224 insert #t select 1 ,' 三 '
225 union all select 2 ,' 乙 '
226 union all select 3 ,' 二 '
227 union all select 4 ,' 一 '
228 union all select 5 ,' 十 '
229
230 select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS
231 drop table #t
232
233 /* 结果：
234 id name
235 ----------- --------------------
236 4 一
237 2 乙
238 3 二
239 5 十
240 1 三
241 */
242
243 --------------------------- 计算汉字的笔划 --------------------------------------------
244 -- 上面所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固
245 -- 定表，为了通用和代码转移方便，把表 tab_hzbh 的内容写在语句内，然后计算用户输入一串
246 -- 汉字的总笔划：
247 USE [tempdb]
248 GO
249 create function fun_getbh (@str nvarchar (4000 ))
250 returns int
251 as
252 begin
253 declare @word nchar ( 1), @n int
254 set @n= 0
255 while len ( @str)> 0
256 begin
257 set @word=left( @str ,1 )
258 -- 如果非汉字，笔划当计
259 set @n= @n +(case when unicode (@word ) between 19968 and 19968 + 20901
260 then ( select top 1 id from (
261 select 1 as id ,N ' 亅' as word
262 union all select 2 ,N ' 阝'
263 union all select 3 ,N ' 马'
264 union all select 4 ,N ' 风'
265 union all select 5 ,N ' 龙'
266 union all select 6 ,N ' 齐'
267 union all select 7 ,N ' 龟'
268 union all select 8 ,N ' 齿'
269 union all select 9 ,N ' 鸩'
270 union all select 10 ,N ' 龀'
271 union all select 11 ,N ' 龛'
272 union all select 12 ,N ' 龂'
273 union all select 13 ,N ' 龆'
274 union all select 14 ,N ' 龈'
275 union all select 15 ,N ' 龊'
276 union all select 16 ,N ' 龍'
277 union all select 17 ,N ' 龠'
278 union all select 18 ,N ' 龎'
279 union all select 19 ,N ' 龐'
280 union all select 20 ,N ' 龑'
281 union all select 21 ,N ' 龡'
282 union all select 22 ,N ' 龢'
283 union all select 23 ,N ' 龝'
284 union all select 24 ,N ' 齹'
285 union all select 25 ,N ' 龣'
286 union all select 26 ,N ' 龥'
287 union all select 27 ,N ' 齈'
288 union all select 28 ,N ' 龞'
289 union all select 29 ,N ' 麷'
290 union all select 30 ,N ' 鸞'
291 union all select 31 ,N ' 麣'
292 union all select 32 ,N ' 龖'
293 union all select 33 ,N ' 龗'
294 union all select 35 ,N ' 齾'
295 union all select 36 ,N ' 齉'
296 union all select 39 ,N ' 靐'
297 union all select 64 ,N ' 龘'
298 ) T
299 where word>= @word collate Chinese_PRC_Stroke_CS_AS_KS_WS
300 order by id ASC) else 0 end )
301 set @str=right( @str ,len ( @str)- 1 )
302 end
303 return @n
304 END
305
306
307 select dbo. fun_getbh (' 中华 '), dbo .fun_getbh ( '中華人民共和國 ' )
308 -- 简繁体都行
309
310
311 -------------------------------------------------------------------------
312 -- 用排序规则的特性得到汉字拼音首字母
313
314 -- 用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：
315 USE [tempdb]
316 GO
317 create function fun_getPY (@str nvarchar (4000 ))
318 returns nvarchar ( 4000)
319 as
320 begin
321 declare @word nchar ( 1), @PY nvarchar( 4000 )
322 set @PY= ''
323 while len ( @str)> 0
324 begin
325 set @word=left( @str ,1 )
326 -- 如果非汉字字符，返回原字符
327 set @PY= @PY +(case when unicode (@word ) between 19968 and 19968 + 20901
328 then ( select top 1 PY from (
329 select 'A' as PY , N' 驁 ' as word
330 union all select 'B', N '簿 '
331 union all select 'C', N '錯 '
332 union all select 'D', N '鵽 '
333 union all select 'E', N '樲 '
334 union all select 'F', N '鰒 '
335 union all select 'G', N '腂 '
336 union all select 'H', N '夻 '
337 union all select 'J', N '攈 '
338 union all select 'K', N '穒 '
339 union all select 'L', N '鱳 '
340 union all select 'M', N '旀 '
341 union all select 'N', N '桛 '
342 union all select 'O', N '漚 '
343 union all select 'P', N '曝 '
344 union all select 'Q', N '囕 '
345 union all select 'R', N '鶸 '
346 union all select 'S', N '蜶 '
347 union all select 'T', N '籜 '
348 union all select 'W', N '鶩 '
349 union all select 'X', N '鑂 '
350 union all select 'Y', N '韻 '
351 union all select 'Z', N '咗 '
352 ) T
353 where word>= @word collate Chinese_PRC_CS_AS_KS_WS
354 order by PY ASC) else @word end )
355 set @str=right( @str ,len ( @str)- 1 )
356 end
357 return @PY
358 end
359
360 select dbo. fun_getPY (' 中华 '), dbo .fun_getPY ( '中華人民共和國 ' )
361 -- 结果都为： ZHRMGHG
362
363 -------------------------------------------------------------------------
364 -- 先用SQLSERVER 方法得到所有汉字，不用字典，我们简单利用 SQL 语句就可以得到：
365 select top 20902 code =identity ( int, 19968 ,1 ) into #t from syscolumns a, syscolumns b
366
367 select code, nchar (code ) as CNWord from #t
368
369 -- 然后，我们用 Select语句，让它按笔划排序。
370
371 select code, nchar (code ) as CNWord
372 from #t
373 order by nchar( code ) collate Chinese_PRC_Stroke_CS_AS_KS_WS, code
