mysql f_getpy_取得拼音字头的存储过程_MySQL

-- =============================================

-- 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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！