SQLSERVER中汉字提取首字母的拼音函数的实现

--创建一个汉字提取首字母的函数
--还存在一点小小的问题(符号?)
create function hs(@a varchar(1000)='')
returns varchar(1000)
as
begin
declare @b varchar(40),@i int,@c varchar(10)
declare @d varchar(10),@e varchar(10),@pinyin nvarchar(2000)
set @i=1
set @pinyin=''
while @i<=len(@a)
begin
set @c=substring(@a,@i,1)
set @e=cast(@c as varbinary)
if @e>=0xB0A1 and @e<=0xB0C4
BEGIN
  set @pinyin=@pinyin+'A'
END
if @e>=0xB0C5 and @e<=0xB2C0
BEGIN
set @pinyin=@pinyin+'B'
END
if @e>=0xB2C1 and @e<=0xB4ED
BEGIN
set @pinyin=@pinyin+'C'
END
if @e>=0xB4EE and @e<=0xB6E9
BEGIN
set @pinyin=@pinyin+'D'
END
if @e>=0xB6EA and @e<=0xB7A1
BEGIN
set @pinyin=@pinyin+'E'
END
if @e>=0xB7A2 and @e<=0xB8C0
BEGIN
set @pinyin=@pinyin+'F'
END
if @e>=0xB8C1 and @e<=0xB9FD
BEGIN
set @pinyin=@pinyin+'G'
END
if @e>=0xB9FE and @e<=0xBBF6
BEGIN
set @pinyin=@pinyin+'H'
END
if @e>=0xBBF7 and @e<=0xBFA5
BEGIN
set @pinyin=@pinyin+'J'
END
if @e>=0xBFA6 and @e<=0xC0AB
BEGIN
set @pinyin=@pinyin+'K'
END
if @e>=0xC0AC and @e<=0xC2E7
BEGIN
set @pinyin=@pinyin+'L'
END
if @e>=0xC2E8 and @e<=0xC4C2
BEGIN
set @pinyin=@pinyin+'M'
END
if @e>=0xC4C3 and @e<=0xC5B5
BEGIN
set @pinyin=@pinyin+'N'
END 
if @e>=0xC5B6 and @e<=0xC5BD
BEGIN
set @pinyin=@pinyin+'O'
END
if @e>=0xC5BE and @e<=0xC6D9
BEGIN
set @pinyin=@pinyin+'P'
END
if @e>=0xC6DA and @e<=0xC8BA
BEGIN
set @pinyin=@pinyin+'Q'
END
if @e>=0xC8BB and @e<=0xC8F5
BEGIN
set @pinyin=@pinyin+'R'
END  
if @e>=0xC8F6 and @e<=0xCBF9
BEGIN
set @pinyin=@pinyin+'S'
END
if @e>=0xCBFA and @e<=0xCDD9
BEGIN
set @pinyin=@pinyin+'T'
END
if @e>=0xCDDA and @e<=0xCEF3
BEGIN
set @pinyin=@pinyin+'W'
END
if @e>=0xCEF4 and @e<=0xD1B8
BEGIN
set @pinyin=@pinyin+'X'
END
if @e>=0xD1B9 and @e<=0xD4D0
BEGIN
set @pinyin=@pinyin+'Y'
END
if @e>=0xD4D1 and @e<=0xD7F9
BEGIN
set @pinyin=@pinyin+'Z'
END
set @i=@i+1
end
return @pinyin
end
posted @ 2013-07-24 16:51  子福当自强  阅读(469)  评论(0编辑  收藏  举报
悟道