获取中文首拼码函数

 1 Create function   [dbo].[f_GetPy](@str   nvarchar(4000)) 
 2 returns   nvarchar(4000) 
 3 as 
 4 begin 
 5 declare   @strlen   int,@re   nvarchar(4000) 
 6 declare   @t   table(chr   nchar(1)   collate   Chinese_PRC_CI_AS,letter   nchar(1)) 
 7 insert   into   @t(chr,letter) 
 8     select   '吖 ', 'A '   union   all   select   '八 ', 'B '   union   all 
 9     select   '嚓 ', 'C '   union   all   select   '咑 ', 'D '   union   all 
10     select   '妸 ', 'E '   union   all   select   '发 ', 'F '   union   all 
11     select   '旮 ', 'G '   union   all   select   '铪 ', 'H '   union   all 
12     select   '丌 ', 'J '   union   all   select   '咔 ', 'K '   union   all 
13     select   '垃 ', 'L '   union   all   select   '嘸 ', 'M '   union   all 
14     select   '拏 ', 'N '   union   all   select   '噢 ', 'O '   union   all 
15     select   '妑 ', 'P '   union   all   select   '七 ', 'Q '   union   all 
16     select   '呥 ', 'R '   union   all   select   '仨 ', 'S '   union   all 
17     select   '他 ', 'T '   union   all   select   '屲 ', 'W '   union   all 
18     select   '夕 ', 'X '   union   all   select   '丫 ', 'Y '   union   all 
19     select   '帀 ', 'Z ' 
20     select   @strlen=len(@str),@re= ' ' 
21     while   @strlen> 0 
22     begin 
23         select   top   1   @re=letter+@re,@strlen=@strlen-1 
24             from   @t   a   where   chr <=substring(@str,@strlen,1) 
25             order   by   chr   desc 
26         if   @@rowcount=0 
27             select   @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 
28     end 
29     return(@re) 
30 end 

效果如下：

转载于:https://www.cnblogs.com/nimin961145/archive/2013/03/18/2965860.html