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

大家讲道理
发布: 2016-11-10 11:22:02
原创
1894 人浏览过

场景:

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

要求:

得到的结果排序应为:

  • x

  • xia

  • xiao

  • 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>&#39;z&#39;
  select @n = @n +1
     ,@c = case chn when @chn then char(@n) else @c end
   from(
    select top 27 * from (
     select chn = &#39;吖&#39;
     union all select &#39;八&#39;
     union all select &#39;嚓&#39;
     union all select &#39;咑&#39;
     union all select &#39;妸&#39;
     union all select &#39;发&#39;
     union all select &#39;旮&#39;
     union all select &#39;铪&#39;
     union all select &#39;丌&#39;  --because have no &#39;i&#39;
     union all select &#39;丌&#39;
     union all select &#39;咔&#39;
     union all select &#39;垃&#39;
     union all select &#39;嘸&#39;
     union all select &#39;拏&#39;
     union all select &#39;噢&#39;
     union all select &#39;妑&#39;
     union all select &#39;七&#39;
     union all select &#39;呥&#39;
     union all select &#39;仨&#39;
     union all select &#39;他&#39;
     union all select &#39;屲&#39;  --no &#39;u&#39;
     union all select &#39;屲&#39;  --no &#39;v&#39;
     union all select &#39;屲&#39;
     union all select &#39;夕&#39;
     union all select &#39;丫&#39;
     union all select &#39;帀&#39;
     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
登录后复制


来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板