笔记228 SQLSERVER 中的排序规则 服务器》数据库》表列

笔记228 SQLSERVER 中的排序规则 服务器》数据库》表列

  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 

 

posted @ 2013-08-04 13:55 桦仔 阅读(...) 评论(...)  编辑 收藏