SQL SERVER取得汉字的拼音缩写

原文在此，http://www.cnblogs.com/wuhuacong/archive/2010/01/25/1655916.html

/*
取得汉字的拼音缩写
*/
CREATE function [dbo].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

执行结果：

select dbo.f_GetPy('博客园')
/*
                    
------------------- 
BKY 

（所影响的行数为 1 行）
*/