SQL取汉字首字母的两个函数.docxVIP

go--创建取汉字首字母函数(第一版)create?function?[dbo].[f_getpy_V1]?(@str?nvarchar(4000))returns?nvarchar(4000)asbegin????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?@PYend?go--创建取汉字首字母函数(第二版)create?function?[dbo].[f_getpy_V2](@Str?varchar(500)=)returns?varchar(500)asbegin????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?@chnz???????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?垃?????