/*--获得汉字字符串的首字母--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fGetPy]GO
--创建取拼音函数create function fGetPy(@Str varchar(500)='')returns varchar(500)asbegin--函数实现开始
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
