sql语句 汉字转拼音首字母

create function getpy(@str varchar(500))
returns varchar(500)
as
begin
declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)
set @cyc=1–从第几个字开始取
set @length=len(@str)–输入汉字的长度
set @str1=”–用于存放返回值
while @cyc<=1
begin
select @charcate=cast(substring(@str,@cyc,1) as varbinary)–每次取出一个字并将其转变成二进制,便于与gbk编码表进行比较
if @charcate>=0xb0a1 and @charcate<=0xb0c4
set @str1=@str1+’a’–说明此汉字的首字母为a,以下同上
else if @charcate>=0xb0c5 and @charcate<=0xb2c0
set @str1=@str1+’b’
else if @charcate>=0xb2c1 and @charcate<=0xb4ed
set @str1=@str1+’c’
else if @charcate>=0xb4ee and @charcate<=0xb6e9
set @str1=@str1+’d’
else if @charcate>=0xb6ea and @charcate<=0xb7a1
set @str1=@str1+’e’
else if @charcate>=0xb7a2 and @charcate<=0xb8c0
set @str1=@str1+’f’
else if @charcate>=0xb8c1 and @charcate<=0xb9fd
set @str1=@str1+’g’
else if @charcate>=0xb9fe and @charcate<=0xbbf6
set @str1=@str1+’h’
else if @charcate>=0xbbf7 and @charcate<=0xbfa5
set @str1=@str1+’j’
else if @charcate>=0xbfa6 and @charcate<=0xc0ab
set @str1=@str1+’k’
else if @charcate>=0xc0ac and @charcate<=0xc2e7
set @str1=@str1+’l’
else if @charcate>=0xc2e8 and @charcate<=0xc4c2
set @str1=@str1+’m’
else if @charcate>=0xc4c3 and @charcate<=0xc5b5
set @str1=@str1+’n’
else if @charcate>=0xc5b6 and @charcate<=0xc5bd
set @str1=@str1+’o’
else if @charcate>=0xc5be and @charcate<=0xc6d9
set @str1=@str1+’p’
else if @charcate>=0xc6da and @charcate<=0xc8ba
set @str1=@str1+’q’
else if @charcate>=0xc8bb and @charcate<=0xc8f5
set @str1=@str1+’r’
else if @charcate>=0xc8f6 and @charcate<=0xcbf9
set @str1=@str1+’s’
else if @charcate>=0xcbfa and @charcate<=0xcdd9
set @str1=@str1+’t’
else if @charcate>=0xcdda and @charcate<=0xcef3
set @str1=@str1+’w’
else if @charcate>=0xcef4 and @charcate<=0xd1b8
set @str1=@str1+’x’
else if @charcate>=0xd1b9 and @charcate<=0xd4d0
set @str1=@str1+’y’
else if @charcate>=0xd4d1 and @charcate<=0xd7f9
set @str1=@str1+’z’
set @cyc=@cyc+1–取出输入汉字的下一个字
end
return @str1–返回输入汉字的首字母
end