场景:
需要通过用户输入的姓名关键字来搜索用户。用户输入关键字'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>'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