数据库表某字段是否可以直接转化成拼音字母首位？

--获取拼音首字母函数

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

--调用

select * from a order by case Name when '其他' then 1 else 0 end,dbo.f_GetPy(Name)











--获取字符串拼音字头

--Pqs 2006-11-20

--@Char 输入的字符串

CREATE function dbo.GetPY (@Char varchar(100))  

returns varchar(100) as 

begin

 --字符串长度		返回数据		遍历字符串位置		遍历的字符

 declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)

 --初始化数据

 --set @str='龙岂池中物'

 set @i_Len = len(@Char)

 set @c_Return = ''

 set @i_pos = 0 

 --遍历字符串 

 while (@i_pos < @i_Len )

 begin

  set @i_pos = @i_pos + 1

  set @c_CN = substring(@Char, @i_pos, 1)

  if (@c_CN > 'z' )

  --中文处理，查询匹配

  begin

         set @c = case when @c_CN>='帀' then 'Z' 	 when @c_CN>='丫' and @c_CN<'帀' then 'Y'	 when @c_CN>='夕' and @c_CN<'丫' then 'X'	 when @c_CN>='屲' and @c_CN<'夕' then 'W'

		 when @c_CN>='他' and @c_CN<'屲' then 'T'	 when @c_CN>='仨' and @c_CN<'他' then 'S'	 when @c_CN>='呥' and @c_CN<'仨' then 'R'	 when @c_CN>='七' and @c_CN<'呥' then 'Q'

		 when @c_CN>='妑' and @c_CN<'七' then 'P'	 when @c_CN>='噢' and @c_CN<'妑' then 'O'	 when @c_CN>='拏' and @c_CN<'噢' then 'N'	 when @c_CN>='嘸' and @c_CN<'拏' then 'M'

		 when @c_CN>='垃' and @c_CN<'嘸' then 'L'	 when @c_CN>='咔' and @c_CN<'垃' then 'K'	 when @c_CN>='丌' and @c_CN<'咔' then 'J'	 when @c_CN>='铪' and @c_CN<'丌' then 'H'

		 when @c_CN>='旮' and @c_CN<'铪' then 'G'	 when @c_CN>='发' and @c_CN<'旮' then 'F'	 when @c_CN>='妸' and @c_CN<'发' then 'E'	 when @c_CN>='咑' and @c_CN<'妸' then 'D'

		 when @c_CN>='嚓' and @c_CN<'咑' then 'C'	 when @c_CN>='八' and @c_CN<'嚓' then 'B'	 when @c_CN>='吖' and @c_CN<'八' then 'A'	 Else '' End

--   	select  top 1 @c = Code  from Code_PY   where name <= @c_CN

  end

  else

  begin

	--过滤字符（除了字符、数字和'.'外的所有字符）

	if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')

		set @c=@c_CN

	else

		set @c=''

  end

  

  set @c_Return=@c_Return+isnull(@c ,'')

 end

 return  upper(@c_Return)

end