获得汉字字符串的首字母(T-SQL函数)

/*--获得汉字字符串的首字母--*/ if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[fGetPy]) and xtype in (NFN, NIF, NTF)) drop function [dbo].[fGetPy] GO

--创建取拼音函数 create function fGetPy(@Str varchar(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--while循环开始 select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1) if @chn>z--if开始 --此处只有一个 select 语句，原理以“字符串排序以及ASCII码表”： select @n = @n +1,@c = --★★★select★★★ case chn --case开始 when @chn then char(@n) --case分支 else @c --case分支 end --case结束 from( --★★★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 丌 --because have no i 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 屲 --no u union all select 屲 --no v 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 else --if对应的else set @c=@chn --if结束

set @return=@return+@c end--while循环结束

return(@return) end--函数实现结束

go --测试 select dbo.fgetpy(魏保光) as 姓名拼音,dbo.fgetpy(ab中c国人) as 中国人 select dbo.fgetpy(刘子良) as 姓名拼音,dbo.fgetpy(ab中c国人) as 中国人 select dbo.fgetpy(吴过) as 姓名拼音,dbo.fgetpy(ab中c国人) as 中国人 select dbo.fgetpy(东北) as 姓名拼音,dbo.fgetpy(ab中c国人) as 中国人 select dbo.fgetpy(王大海八丫) as 王大海八丫,dbo.fgetpy(服務地圖) as 服務地圖

--删除拼音函数 drop function fgetpy