当前位置：首页 > news >正文

SQLServer数据库使用Function实现根据字段内容的拼音首字母进行数据查询

news 2025/8/15 2:34:00

实现SQL首字母查询分两步，第一步建Function，第二步引用新建的Function。

1. 首先需要自定义一个查询的Function，详细SQL如下：

ALTER function [dbo].[GetDataByPY](@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

2. 使用GetDataByPY进行数据查询，其中，SQL中的数字表示根据前几位字符串的首字母去匹配，后面的字符串就代表查询的首字母了，例如：

SELECT * FROM <font color=red>表名</font>
WHERE LEFT(dbo.FuncSysGetPY(<font color=red>字段名</font>), 1) = 'a' SELECT * FROM <font color=red>表名</font>
WHERE LEFT(dbo.FuncSysGetPY(<font color=red>字段名</font>), 2) = 'ab' SELECT * FROM <font color=red>表名</font>
WHERE LEFT(dbo.FuncSysGetPY(<font color=red>字段名</font>), 3) = 'abc' 。。。。