一个效率很高的汉字转拼音首字母的函数

场景：

需要通过用户输入的姓名关键字来搜索用户。用户输入关键字'x'来搜索用户（数据来源于表[Name字段中]或内存[List]中）

要求：

得到的结果排序应为：

• x

• xia

• xiao

  

  绘蛙-多图成片

  绘蛙新推出的AI图生视频工具

  下载

• yx

即：

包含x字母的结果均应显示出来

首字母匹配的结果应该排在前面(如x开头)

在条件2相同的前提下更短的结果应排在前面(如x排在xia前面)

create function [dbo].[fn_getpy2](@Str varchar(500)='')
returns varchar(500)
as
begin
 declare @strlen int,@return varchar(500),@ii int
 declare @n int,@c char(1),@chn nchar(1)

 select @strlen=len(@str),@return='',@ii=0
 set @ii=0
 while @ii<@strlen
 begin
  select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
  if @chn>'z'
  select @n = @n +1
     ,@c = case chn when @chn then char(@n) else @c end
   from(
    select top 27 * from (
     select chn = '吖'
     union all select '八'
     union all select '嚓'
     union all select '咑'
     union all select '妸'
     union all select '发'
     union all select '旮'
     union all select '铪'
     union all select '丌'  --because have no 'i'
     union all select '丌'
     union all select '咔'
     union all select '垃'
     union all select '嘸'
     union all select '拏'
     union all select '噢'
     union all select '妑'
     union all select '七'
     union all select '呥'
     union all select '仨'
     union all select '他'
     union all select '屲'  --no 'u'
     union all select '屲'  --no 'v'
     union all select '屲'
     union all select '夕'
     union all select '丫'
     union all select '帀'
     union all select @chn) as a
    order by chn COLLATE Chinese_PRC_CI_AS 
   ) as b
  else set @c=@chn
  set @return=@return+@c
 end
 return(@return)
end