sql serverピンインでソートする機能

2166 ワード

A阿杜阿牛阿翔B Bjack C陳小春成龍陳百強陳冠蒲陳冠希陳浩民陳慶祥陳昇陳司翰陳暁東陳奕迅陳明章D杜徳偉迪克牛仔邓建明邓健泓E F范逸臣方力申费玉清房祖名范宗沛G恭碩良古巨基古天楽光良郭富城郭品超郭伟亮H黄品源黄貫中黄国俊黄維得黄耀明黄義達黄立行黄家強I J姜育恒K孔令奇L劉徳華羅文李克勤李聖傑李宗盛梁朝偉梁漢文林海峰林俊傑林祐威林志炫林志穎林子良林子祥M馬俊偉
 
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--       ,     
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N' ' as word
union all select 'B',N' '
union all select 'C',N' '
union all select 'D',N' '
union all select 'E',N' '
union all select 'F',N' '
union all select 'G',N' '
union all select 'H',N' '
union all select 'J',N' '
union all select 'K',N' '
union all select 'L',N' '
union all select 'M',N' '
union all select 'N',N' '
union all select 'O',N' '
union all select 'P',N' '
union all select 'Q',N' '
union all select 'R',N' '
union all select 'S',N' '
union all select 'T',N' '
union all select 'W',N' '
union all select 'X',N' '
union all select 'Y',N' '
union all select 'Z',N' '
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

クエリ文:
--関数呼び出しの例:--select dbo.fun_getPY('中華人民共和国')はすべてのAから始まる.select id_,dbo.fun_getPY(tit) from tab where dbo.fun_getPY(tit) like 'A%' order by dbo.fun_getPY(tit)Bの先頭の類似:select id_,dbo.fun_getPY(tit) from tab where dbo.fun_getPY(tit) like 'B%' order by dbo.fun_getPY(tit)