取得拼音字头的存储过程

-- =============================================
-- create scalar function (nwgetpyfirst)
-- =============================================
if exists (select *
 from   sysobjects
 where  name = n'nwgetpyfirst')
 drop function nwgetpyfirst
go

create function nwgetpyfirst
(@str varchar(500) = '')
returns varchar(500)
as
begin
 declare @strlen int,
  @return varchar(500),
  @ii int,
  @c char(1),
  @chn nchar(1)
 --//初始化变量
 declare @pytable table(
 chn char(2) collate chinese_prc_cs_as not null,
 py char(1) collate chinese_prc_cs_as null,
 primary key (chn)
   )
 insert into @pytable values('吖', 'a')
 insert into @pytable values('八', 'b')
 insert into @pytable values('嚓', 'c')
 insert into @pytable values('咑', 'd')
 insert into @pytable values('妸', 'e')
 insert into @pytable values('发', 'f')
 insert into @pytable values('旮', 'g')
 insert into @pytable values('铪', 'h')
 insert into @pytable values('丌', 'i')
 --insert into @pytable values('丌', 'j')
 insert into @pytable values('咔', 'k')
 insert into @pytable values('垃', 'l')
 insert into @pytable values('嘸', 'm')
 insert into @pytable values('拏', 'n')
 insert into @pytable values('噢', 'o')
 insert into @pytable values('妑', 'p')
 insert into @pytable values('七', 'q')
 insert into @pytable values('呥', 'r')
 insert into @pytable values('仨', 's')
 insert into @pytable values('他', 't')
 insert into @pytable values('屲', 'u')
 --insert into @pytable values('屲', 'v')
 --insert into @pytable values('屲', 'w')
 insert into @pytable values('夕', 'x')
 insert into @pytable values('丫', 'y')
 insert into @pytable values('帀', 'z')

 select @strlen = len(@str), @return = '', @ii = 0
 --//循环整个字符串,用拼音的首字母替换汉字
 while @ii < @strlen
 begin
  select @ii = @ii + 1, @chn = substring(@str, @ii, 1)
  if @chn > 'z' --//检索输入的字符串中有中文字符
   select @c = max(py)
   from @pytable
   where chn <= @chn
  else
   set @[email protected]
 
  set @[email protected][email protected]
 end
 return @return
end
go

-- =============================================
-- example to execute function
-- =============================================
select dbo.nwgetpyfirst('梦想国度'), dbo.nwgetpyfirst('noctwolf分享源码'), dbo.nwgetpyfirst('')
go