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

本文链接：https://blog.csdn.net/zlbdmm/article/details/104689199

本文介绍了一种在SQLServer中实现的拼音检索函数，该函数能够接收中文输入并返回对应的拼音首字母，适用于中文信息的快速检索和排序。通过详细的代码示例，展示了如何创建和使用这一函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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