取得拼音字头的存储过程

2022-07-15 80酷酷网 80kuku.com

　　存储过程|拼音

-- =============================================
-- Create scalar function (NWGetPYFirst)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NWGetPYFirst')
DROP FUNCTION NWGetPYFirst
GO

CREATE FUNCTION NWGetPYFirst
(str varchar(500) = '')
RETURNS varchar(500)
AS
BEGIN
Declare strlen int,
return varchar(500),
ii int,
c char(1),
chn nchar(1)
--//初始化变量
Declare pytable table(
chn char(2) COLLATE Chinese_PRC_CS_AS NOT NULL,
py char(1) COLLATE Chinese_PRC_CS_AS NULL,
PRIMARY KEY (chn)
)
insert into pytable values('吖', 'A')
insert into pytable values('八', 'B')
insert into pytable values('嚓', 'C')
insert into pytable values('咑', 'D')
insert into pytable values('妸', 'E')
insert into pytable values('发', 'F')
insert into pytable values('旮', 'G')
insert into pytable values('铪', 'H')
insert into pytable values('丌', 'I')
--insert into pytable values('丌', 'J')
insert into pytable values('咔', 'K')
insert into pytable values('垃', 'L')
insert into pytable values('嘸', 'M')
insert into pytable values('拏', 'N')
insert into pytable values('噢', 'O')
insert into pytable values('妑', 'P')
insert into pytable values('七', 'Q')
insert into pytable values('呥', 'R')
insert into pytable values('仨', 'S')
insert into pytable values('他', 'T')
insert into pytable values('屲', 'U')
--insert into pytable values('屲', 'V')
--insert into pytable values('屲', 'W')
insert into pytable values('夕', 'X')
insert into pytable values('丫', 'Y')
insert into pytable values('帀', 'Z')

select strlen = len(str), return = '', ii = 0
--//循环整个字符串,用拼音的首字母替换汉字
while ii < strlen
begin
select ii = ii + 1, chn = substring(str, ii, 1)
if chn > 'z' --//检索输入的字符串中有中文字符
   SELECT c = max(py)
   FROM pytable
   where chn <= chn
else
   set c=chn

set return=return+c
end
return return
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.NWGetPYFirst('梦想国度'), dbo.NWGetPYFirst('noctwolf分享源码'), dbo.NWGetPYFirst('')
GO