通过SQL Server取出汉字的拼音.docx

1、通过SQL Server取出汉字的拼音通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQLSERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误： 无法解决 equal to 操作的排序规则冲突。一.错误分析：这个错误是因为排序规则不一致造成的，我们做个测试，比如：create table #t1(name varchar(20) collate Albanian_CI_AI_WS, value int)create t

2、able #t2(name varchar(20) collate Chinese_PRC_CI_AI_WS, value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on A.name=B.name 这样，错误就出现了： 服务器: 消息 446，级别 16，状态 9，行 1 无法解决 equal to 操作的排序规则冲突。要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就不再出现了。语句这样写：select * from #t1 A inner join #t2 B on A.name=B.name collat

3、e Chinese_PRC_CI_AI_WS二.排序规则简介： 什么叫排序规则呢？MS是这样描述的：在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。在查询分析器内执行下面语句，可以得到SQLSERVER支持的所有排序规则。select * from :fn_helpcollations() 排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。如：Chinese_PRC_CS_AI_WS 前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的

4、排序规则。排序规则的后半部份即后缀 含义： _BIN 二进制排序 _CI(CS) 是否区分大小写，CI不区分，CS区分_AI(AS) 是否区分重音，AI不区分，AS区分_KI(KS) 是否区分假名类型,KI不区分，KS区分 _WI(WS) 是否区分宽度 WI不区分，WS区分区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项， 比较还将重音不同的字母视为不等。区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项三.排序规

5、则的应用：SQL SERVER提供了大量的WINDOWS和SQLSERVER专用的排序规则，但它的应用往往被开发人员所忽略。其实它在实践中大有用处。例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

6、 - - 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 三*/四.在实

7、践中排序规则应用的扩展SQL SERVER汉字排序规则可以按拼音、笔划等排序，那么我们如何利用这种功能来处理汉字的一些难题呢？我现在举个例子：用排序规则的特性计算汉字笔划要计算汉字笔划，我们得先做准备工作，我们知道，WINDOWS多国汉字，UNICODE目前收录汉字共20902个。简体GBK码汉字UNICODE值从19968开始。首先，我们先用SQLSERVER方法得到所有汉字，不用字典，我们简单利用SQL语句就可以得到：select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b再用以下语

8、句，我们就得到所有汉字，它是按UNICODE值排序的：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结果：code CNWord - - 19968 一20008 丨20022 丶20031 丿20032 乀20033 乁20057 乙20058 乚20059 乛20101 亅19969 丁. 从上面的

9、结果，我们可以清楚的看到，一笔的汉字，code是从19968到20101，从小到大排，但到了二笔汉字的第一个字丁，CODE为19969，就不按顺序而重新开始了。有了这结果，我们就可以轻松的用SQL语句得到每种笔划汉字归类的第一个或最后一个汉字。下面用语句得到最后一个汉字：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_PR

10、C_Stroke_CS_AS_KS_WS,codeselect A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.codeB.code where B.code is nullorder by A.id得到36个汉字，每个汉字都是每种笔划数按Chinese_PRC_Stroke_CS_AS_KS_WS排序规则排序后的最后一个汉字：亅阝马风龙齐龟齿鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘上面可以看出：亅是所有一笔汉字排序后的最后一个字，阝是所有二笔汉字排序后的最后一个字.等等。但同时也发现，从第33个汉字龗(33笔)

11、后面的笔划有些乱，不正确。但没关系，比龗笔划多的只有四个汉字，我们手工加上：齾35笔，齉36笔，靐39笔，龘64笔建汉字笔划表（TAB_HZBH）：create table tab_hzbh(id int identity,cnword nchar(1)-先插入前33个汉字insert tab_hzbhselect top 33 A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.code=a collate Chinese_PRC_Stroke_CS_AS_KS_WSorder by idid - 8(结果：汉字国笔划数为8)上面

12、所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固定表，为了通用和代码转移方便，把表tab_hzbh的内容写在语句内，然后计算用户输入一串汉字的总笔划：create function fun_getbh(str nvarchar(4000)returns intasbegindeclare word nchar(1),n intset n=0while len(str)0beginset word=left(str,1)-如果非汉字，笔划当0计set n=n+(case when unicode(word) between 19968 and 19968+20901the

13、n (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龂 unio

14、n 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

15、 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 C

16、hinese_PRC_Stroke_CS_AS_KS_WSorder by id ASC) else 0 end)set str=right(str,len(str)-1)endreturn nend-函数调用实例：select dbo.fun_getbh(中华人民共和国),dbo.fun_getbh(中華人民共和國)执行结果：笔划总数分别为39和46，简繁体都行。当然，你也可以把上面UNIONALL内的汉字和笔划改存在固定表内，在汉字列建CLUSTERED INDEX，列排序规则设定为： Chinese_PRC_Stroke_CS_AS_KS_WS这样速度更快。如果你用的是BIG5码的操作系

17、统，你得另外生成汉字，方法一样。但有一点要记住：这些汉字是通过SQL语句SELECT出来的，不是手工输入的，更不是查字典得来的，因为新华字典毕竟不同于UNICODE字符集，查字典的结果会不正确。 用排序规则的特性得到汉字拼音首字母用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：create function fun_getPY(str nvarchar(4000)returns nvarchar(4000)asbegindeclare word nchar(1),PY nvarchar(4000)set PY=while len(str)0beginset word=le

18、ft(str,1)-如果非汉字字符，返回原字符set PY=PY+(case when unicode(word) between 19968 and 19968+20901then (select top 1 PY from (select A as PY,N驁 as wordunion 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 sel

19、ect 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,

20、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)endreturn PYend-函数调用实例：select dbo.fun_getPY(中华人民共和国),dbo.fun_getPY(中華人民共和國)结果都为：ZHRMGHG 你若有兴趣，也可用相同的方法，扩展为得到汉字全拼的函数，甚至还可以得到全拼的读音声调，不过全拼分类大多了。得到全拼最好是用对照表，两万多汉字搜索速度很快，用对照表还可以充分利用表的索引。 排序规则

21、还有很多其它的巧妙用法，限于篇幅在此就不再详细说明。欢迎大家共同探讨。通过SQLServer取出汉字的拼音/*-1.-获得汉字字符串的首字母 根据大力的贴子改成.将大力的两个函数合并成了一个函数. 可以应用于助记码的查询-*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.fGetPy) and xtype in (NFN, NIF, NTF)drop function dbo.fGetPyGO-创建取拼音函数create function fGetPy(Str varchar(500)=)returns va

22、rchar(500)asbegindeclare strlen int,return varchar(500),ii intdeclare n int,c char(1),chn nchar(1)select strlen=len(str),return=,ii=0set ii=0while iizselect n = n +1,c = case chn when chn then char(n) else c endfrom(select top 27 * from (select chn = 吖union all select 八union all select 嚓union all se

23、lect 咑union all select 妸union all select 发union all select 旮union all select 铪union all select 丌-because have no iunion all select 丌union all select 咔union all select 垃union all select 嘸union all select 拏union all select 噢union all select 妑union all select 七union all select 呥union all select 仨union

24、all select 他union all select 屲-no uunion all select 屲-no vunion all select 屲union all select 夕union all select 丫union all select 帀union all select chn) as aorder by chn COLLATE Chinese_PRC_CI_AS ) as belse set c=aset return=return+cendreturn(return)endgo-测试select dbo.fgetpy(东莞市) as 东莞市,dbo.fgetpy(ab

25、中c国人) as 中国人-删除拼音函数drop function fgetpy/*2.-获得汉字拼音的函数 需要创建一个拼音表,包含所有汉字的发音,这个可以通过转换全拼输入法的编码库得到,这里仅举了一个简单的例子.-*/-创建汉字拼音库create table YingShe(CHR char(2),PY varchar(10)insert YingShe select 长,changunion all select 长,zhangunion all select 城,chengunion all select 科,kelunion all select 技,jiunion all select 金,jinunion all select 立,liunion all select 章,zhangunion all select 公,gongunion all select 司,si/*-下面是两个函数,一个以表的形式返回某个字符串的全部拼音,一个返回某某个字符串的其中一个拼音-*/-获取汉字拼音的函数-返回所有的拼音create function f_getpy_tb(str varchar(100)returns