mssql sqlserver sql脚本中快速提取汉字拼音中首字母的方法分享


摘要:
下文分享sql脚本中快速提取汉字拼音中首字母的方法,如下所示:
实验环境:sql server 2008 R2

实现思路:
编写一个sql函数,根据汉字所在的范围判断出汉字的首字母,如下:



create   function   [dbo].[fn_hanZiGetPY]
(@tmp   nvarchar(4000)) 
returns   nvarchar(4000) 
as 
begin 
declare   @tmpLength   int,@tmpReturn   nvarchar(4000) ,@t nvarchar(1)
set @tmpLength =len(@tmp)
set @tmpReturn = ' '
    while   @tmpLength > 0
    begin 
set  @t=substring(@tmp,@ tmpLength,1)
  
   select  @tmpLength =@tmpLength-1,@tmpReturn =
     case 
     when @t>='帀' then 'Z'
     when @t>='丫' then 'Y'
     when @t>='夕' then 'X'
     when @t>='屲' then 'W'
     when @t>='他' then 'T'
     when @t>='仨' then 'S'
     when @t>='呥' then 'R'
     when @t>='七' then 'Q'
     when @t>='妑' then 'P'
     when @t>='噢' then 'O'
     when @t>='拏' then 'N'
     when @t>='嘸' then 'M'
     when @t>='垃' then 'L'
     when @t>='咔' then 'K'
     when @t>='丌' then 'J'
     when @t>='铪' then 'H'
     when @t>='旮' then 'G'
     when @t>='发' then 'F'
     when @t>='妸' then 'E'
     when @t>='咑' then 'D'
     when @t>='嚓' then 'C'
     when @t>='八' then 'B'
     when @t>='吖' then 'A'
     else  @t  end+@tmpReturn
    end 
    return(@tmpReturn) 
end
go


select [dbo].[fn_hanZiGetPY]('猫猫小屋')