[MSSQL]取汉字首字母的两个函数

go --创建取汉字首字母函数(第一版) create function [dbo].[f_getpy_V1] (@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 go --创建取汉字首字母函数(第二版) create function [dbo].[f_getpy_V2](@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='a' set @return=@return+@c end return(@return) end --测试示例 select dbo.[f_getpy_V1]('王立国') as V11 select dbo.[f_getpy_V2]('王立国') as V21 select dbo.[f_getpy_V1]('重复') as V21 select dbo.[f_getpy_V2]('重复') as V22 --运行结果 /* V11 -------- WLG V21 -------- WLG V21 -------- ZF V22 -------- ZF */ --由上我们可以看到，两种方法都没有解决多音字的问题。