欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql语句 汉字转拼音首字母

程序员文章站 2022-05-27 22:34:19
create function GetPY(@str varchar(500))returns varchar(500)asbegin declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20) set @cyc=1 ......

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