用自定义函数实现中文字首字母的查询

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetPy]') and xtype in (N'FN', N'IF', N'TF'))
 drop function [dbo].[f_GetPy]
GO

--创建取拼音函数
create function f_GetPy(@Str nvarchar(400))
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 @t 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

--测试
select * from tabel_name  where  dbo.f_GetPy(name) like  'z%'