SQLServer中定义拼音检索函数，根据中文参数返回对应汉字的拼音首字母

╰半夏微凉° 2023-07-11 15:02 10阅读 0赞

### SQLServer中定义拼音检索函数，根据中文参数返回对应汉字的拼音首字母 ###

直接上代码如下：

/********************************************************************************* * Copyright (C) 2013,All Rights Reserved * * Function: FuncSysGetPY * * Description: * * 拼音检索函数，根据中文参数返回对应汉字的拼音首字母 * * Author: * * zhlb * * Finish DateTime: * * 2013年2月27日 * * History: * * * * Example: * * select PY=dbo.FuncSysGetPY('汉字测试') * *********************************************************************************/
    create function [dbo].[FuncSysGetPY](@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