場景：
需要通過用戶輸入的姓名關(guān)鍵字來搜索用戶。用戶輸入關(guān)鍵字’x’來搜索用戶（數(shù)據(jù)來源于表[Name字段中]或內(nèi)存[List
要求：
得到的結(jié)果排序應(yīng)為：
-
x
-
xia
-
xiao
-
yx
即：
包含x字母的結(jié)果均應(yīng)顯示出來
首字母匹配的結(jié)果應(yīng)該排在前面(如x開頭)
在條件2相同的前提下更短的結(jié)果應(yīng)排在前面(如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'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?'發(fā)' ?????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
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
