-- 作者:admin
-- 发布时间:2008/12/19 7:00:23
-- [转帖]SQL中如何获取汉字的拼音开头字母?
推荐网络资料:http://topic.csdn.net/u/20070920/11/95cb8dbe-1044-4bf8-b0d2-9c4be0f31200.html?774765209
我试了一下,都可以(没有完全测试):
1、 create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @strlen int,@re nvarchar(4000) declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) insert into @t(chr,letter) select \'吖 \', \'A \' union all select \'八 \', \'B \' union all select \'嚓 \', \'C \' union all select \'咑 \', \'D \' union all select \'妸 \', \'E \' union all select \'发 \', \'F \' union all select \'旮 \', \'G \' union all select \'铪 \', \'H \' union all select \'丌 \', \'J \' union all select \'咔 \', \'K \' union all select \'垃 \', \'L \' union all select \'嘸 \', \'M \' union all select \'拏 \', \'N \' union all select \'噢 \', \'O \' union all select \'妑 \', \'P \' union all select \'七 \', \'Q \' union all select \'呥 \', \'R \' union all select \'仨 \', \'S \' union all select \'他 \', \'T \' union all select \'屲 \', \'W \' union all select \'夕 \', \'X \' union all select \'丫 \', \'Y \' union all select \'帀 \', \'Z \' select @strlen=len(@str),@re= \' \' while @strlen> 0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a where chr <=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end go
用法为: print dbo.f_GetPy(\'南京财经大学\')
2、 create function get_spell (@str varchar(2)) returns varchar(1) as begin declare @codedata varchar (8000) set @codedata = \'CJWGNSPGCENEGYPBTWXZDXYKYGTPJNMJQMBSGZSCYJSYYFPGGBZGYDYWJKGALJSWKBJQHYJWPDZLSGMRYBYWWCCGZNKYDGTTNGJEYEKZYDCJNMCYLQLYPYQBQRPZSLWBDGKJFYXJWCLTBNCXJJJJCXDTQSQZYCDXXHGCKBPHFFSSPYBGMXJBBYGLBHLSSMZMPJHSOJNGHDZCDKLGJHSGQZHXQGKEZZWYMCSCJNYETXADZPMDSSMZJJQJYZCJJFWQJBDZBJGDNZCBWHGXHQKMWFBPBQDTJJZKQHYLCGXFPTYJYYZPSJLFCHMQSHGMMXSXJPKDCMBBQBEFSJWHWWGCKPYLQBGLDLCCTNMAEDDKSJNGKCSGXLHZAYBDBTSDKDYLHGYMYLCXPYCJNDQJWXQXFYYFJLEJBZRWCCQHQCSBZKYMGPLBMCRQCFLNYMYQMSQTRBCJTHZTQFRXCHXMCJCJLXQGJMSHZKBSWXEMDLCKFSYDSGLYCJJSSJNQBJCTYHBFTDCYJDGWYGHQFRXWCKQKXEBPDJPXJQSRMEBWGJLBJSLYYSMDXLCLQKXLHTJRJJMBJHXHWYWCBHTRXXGLHJHFBMGYKLDYXZPPLGGPMTCBBAJJZYLJTYANJGBJFLQGDZYQCAXBKCLECJSZNSLYZHLXLZCGHBXZHZNYTDSBCJKDLZAYFFYDLABBGQSZKGGLDNDNYSKJSHDLXXBCGHXYGGDJMMZNGMMCCGWZSZXSJBZNMLZDTHCQYDBDLLSCDDNLKJYHJSYCJLKOHQASDHNHCSGAEHDAASHTCPLCPQYBSDMPJLPCJAQLCDHJJASPRCHNGJNLHLYYQYHWZPNCCGWWMZFFJQQQQXXACLBHKDJXDGMMYDJXZLLSYGXGKJRYWZWYCLZMCSJZLDBNDCFCXYHLSCHYCJQPPQAGMNYXPFRKSSBJLYXYJJGLNSCMHCWWMNZJJLHMHCHSYPPTTXRYCSXBYHCSMXJSXNBWGPXXTAYBGAJCXLYPDCCWQOCWKCCSBNHCPDYZNBCYYTYCKSKYBSQKKYTQQXFCWCHCWKELCQBSQYJQCCLMTHSYWHMKTLKJLYCHWHEQJHTJHPPQPQSCFYMMCMGBMHGLGSLLYSDLLLJPCHMJHWLJCYHZJXHDXJLHXRSWLWZJCBXMHZQXSDZPMGFCSGLSDYMJSHXPJXOMYQKNMYBLRTHBCFTPMGYXLCHLHLZYLXGSSSSCCLSLDCLEPBHSHXYYFHBMGDFYCNJQWLQHJJCYWJZTEJJDHFBLQXTQKWHDCHQXAGTLXLJXMSLJHDZKZJECXJCJNMBBJCSFYWKBJZGHYSDCPQYRSLJPCLPWXSDWEJBJCBCNAYTMGMBAPCLYQBCLZXCBNMSGGFNZJJBZSFQYNDXHPCQKZCZWALSBCCJXPOZGWKYBSGXFCFCDKHJBSTLQFSGDSLQWZKXTMHSBGZHJCRGLYJBPMLJSXLCJQQHZMJCZYDJWBMJKLDDPMJEGXYHYLXHLQYQHKYCWCJMYHXNATJHYCCXZPCQLBZWWWTWBQCMLBMYNJCCCXBBSNZZLJPLJXYZTZLGCLDCKLYRZZGQTGJHHGJLJAXFGFJZSLCFDQZLCLGJDJCSNCLLJPJQDCCLCJXMYZFTSXGCGSBRZXJQQCCZHGYJDJQQLZXJYLDLBCYAMCSTYLBDJBYREGKLZDZHLDSZCHZNWCZCLLWJQJJJKDGJCOLBBZPPGLGHTGZCYGEZMYCNQCYCYHBHGXKAMTXYXNBSKYZZGJZLQJDFCJXDYGJQJJPMGWGJJJPKJSBGBMMCJSSCLPQPDXCDYYKYPCJDDYYGYWCHJRTGCNYQLDKLJCZZGZCCJGDYKSGPZMDLCPHNJAFYZDJCNMWESCSGLBTZCGMSDLLYXQSXSBLJSBBSGGHFJLWPMZJNLYYWDQSHZXTYYWHMCYHYWDBXBTLMSWYYFSBJCBDXXLHJHFPSXZQHFZMQCZTQCXZXRDKDJHNNYZQQFNQDMMGNYDXMJGDHCDYCBFFALLZTDLTFKMXQZDNGEQDBDCZJDXBZGSQQDDJCMBKXFFXMKDMCSYCHZCMLJDJYNHPRSJMKMPCKLGDBQTFZSWTFGGLYPLLJZHGJJGYPZLTCSMCNBTJBHFKDHBYZGKPBBYMTDLSXSBNPDKLEYCJNYCDYKZDDHQGSDZSCTARLLTKZLGECLLKJLJJAQNBDGGGHFJTZQJSECSHALQFMMGJNLYJBBTMLYCXDCJPLDLPCQDHSYCBZSCKBZMSLJFLHRBJSNBRGJHXPDGDJYBZGDLGCSEZGXLBLGYXTWMABCHECMWYJYZLLJJSHLGNDJLSLYGKDZPZXJYYZLPCXSZFGWYYDLYHCLJSCMBJHBLYJLYCBLYDPDQYSXKTBYTDKDXJYPCNRJMFDJGKLCCJBCTBJDDBBLBLCDQRPPXJCGLZCSHLTOLJNMDDDLNGKAQAKGJGYHHEZNMSHRPHQQJCHGMFPRXCJGDYCHGHLYRZQLCNGJNZSQDKQJYMSZSWLCFQJQXGBGGXMDJWLMCRNFKKFSYYLJBMQAMMMYCCTBSHCPTXXZZSMPHFSHMCLMLDJFYQXSDYJDJJZZHQPDSZGLSSJBCKBXYQZJSGPSXJZQZNQTBDKWXJKHHGFLBCSMDLDGDZDBLZKYCQNNCSYBZBFGLZZXSWMSCCMQNJQSBDQSJTXXMBLDXCCLZSHZCXRQJGJYLXZFJPHYMZQQYDFQJJLCZNZJCDGZYGCDXMZYSCTLKPHTXHTLBJXJLXSCDQCCBBQJFQZFSLTJBTKQBSXJJLJCHCZDBZJDCZJCCPRNLQCGPFCZLCLCXZDMXMPHGSGZGSZZQJXLWTJPFSYASLCJBTCKWCWMYTCSJJLJCQLWZMALBXYFBPNLSCHTGJWEJJXXGLLJSTGSHJQLZFKCGNNDSZFDEQFHBSAQDGYLBXMMYGSZLDYDJMJJRGBJGKGDHGKBLGKBDMBYLXWCXYTTYBKMRJJZXQJBHLMHMJJZMQASLDCYXYQDLQCAFYWYXQHZ\' declare @gbcode int declare @l int declare @r int
set @l = ASCII(@str) - 160
set @r = cast((substring(CAST(@str as varbinary), 2,1)) as int) - 160
if(@l <= 0 or @r <= 0)begin return @str end
set @gbcode = @l * 100 + @r
declare @spell varchar(1) if (@gbcode >= 1601 and @gbcode < 1637) begin set @spell = \'A\' end else if (@gbcode >= 1637 and @gbcode < 1833) set @spell = \'B\' else if (@gbcode >= 1833 and @gbcode < 2078) set @spell = \'C\' else if (@gbcode >= 2078 and @gbcode < 2274) set @spell = \'D\' else if (@gbcode >= 2274 and @gbcode < 2302) set @spell = \'E\' else if (@gbcode >= 2302 and @gbcode < 2433) set @spell = \'F\' else if (@gbcode >= 2433 and @gbcode < 2594) set @spell = \'G\' else if (@gbcode >= 2594 and @gbcode < 2787) set @spell = \'H\' else if (@gbcode >= 2787 and @gbcode < 3106) set @spell = \'J\' else if (@gbcode >= 3106 and @gbcode < 3212) set @spell = \'K\' else if (@gbcode >= 3212 and @gbcode < 3472) set @spell = \'L\' else if (@gbcode >= 3472 and @gbcode < 3635) set @spell = \'M\' else if (@gbcode >= 3635 and @gbcode < 3722) set @spell = \'N\' else if (@gbcode >= 3722 and @gbcode < 3730) set @spell = \'O\' else if (@gbcode >= 3730 and @gbcode < 3858) set @spell = \'P\' else if (@gbcode >= 3858 and @gbcode < 4027) set @spell = \'Q\' else if (@gbcode >= 4027 and @gbcode < 4086) set @spell = \'R\' else if (@gbcode >= 4086 and @gbcode < 4390) set @spell = \'S\' else if (@gbcode >= 4390 and @gbcode < 4558) set @spell = \'T\' else if (@gbcode >= 4558 and @gbcode < 4684) set @spell = \'W\' else if (@gbcode >= 4684 and @gbcode < 4925) set @spell = \'X\' else if (@gbcode >= 4925 and @gbcode < 5249) set @spell = \'Y\' else if (@gbcode >= 5249 and @gbcode <= 5589) set @spell = \'Z\' else if (@gbcode >= 5601 and @gbcode <= 8794) begin declare @i int set @i = (@l - 56) * 94 + @r set @spell = substring(@codedata, @i, 1) end else begin
set @spell = @str end
return(@spell) end
go
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER function get_spell (@str varchar(512)) returns varchar(512) as begin declare @rs varchar(512) set @rs = \'\' --获得汉字的首字母 --liuhy declare @strlen int
select @strlen = len(@str)
while @strlen>0 begin select @rs = dbo.get_spell_1(substring(@str, @strlen, 1)) + @rs set @strlen = @strlen-1 end return (@rs) end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
用法为: print dbo.get_spell(\'丽\')
[此贴子已经被作者于2010-12-11 20:05:49编辑过]
|