获取拼音首字母函数

--返回汉字首字母 create function [dbo].[chinese_firstletter] ( @str nvarchar(1) ) returns nvarchar(1) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' 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) return @PY end go Select dbo.chinese_firstletter('福田区第五医院') drop function chinese_firstletter /* ---- F （所影响的行数为 1 行） */ ]--获取拼音首字母函数 create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @strlen int,@re nvarchar(4000) declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) insert into @t(chr,letter) select '吖','A' union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z' select @strlen=len(@str),@re='' while @strlen>0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a where chr<=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end go --调用 create table tb(col varchar(10)) insert into tb values('潇洒老乌龟') insert into tb values('孤星') go select 按拼音首字母 = dbo.f_GetPy(col) from tb drop table tb drop function f_GetPy /* 按拼音首字母 ------------ XSLWG GX (2 行受影响) */