中文首字母搜素的实现 sql函数

最新推荐文章于 2024-05-09 00:45:37 发布

转载最新推荐文章于 2024-05-09 00:45:37 发布 · 67 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/alanjl/archive/2013/05/29/3106886.html

-- ============================================= 
-- Author: Jinlong 
-- Create date: 2012-4-17 
-- Description: 提供中文首字母 
-- ============================================= 
CREATE FUNCTION fun_getPY 
( 
@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

后台调用

 wheresql = "User_Chinesename in (SELECT User_Chinesename FROM LXSGL_User WHERE dbo.fun_getPY(User_Chinesename) LIKE N'%" + Txt_CXZ.Text.Trim() + "%')";

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Chenxf>
-- Create date: <2009-12-15>
-- Description: <汉语拼音首字简码>
-- =============================================
CREATE FUNCTION [Basic].[funGetPinYin] 
(
@Str nvarchar(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) 
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 '丌' 
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 '屲' 
union all select '屲' 
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 
set @return=@return+@c 
end 
RETURN(@return) 

END

转载于:https://www.cnblogs.com/alanjl/archive/2013/05/29/3106886.html