SQL获取汉字拼音的打头字母

最新推荐文章于 2019-07-25 23:23:16 发布

转载最新推荐文章于 2019-07-25 23:23:16 发布 · 97 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wangpei/archive/2010/01/11/1644284.html

本文介绍了一个SQL Server中自定义的函数f_GetPy，该函数能够将输入的中文字符串转换为对应的汉语拼音首字母缩写。通过创建临时表并插入汉语拼音对照表的方式，实现了中文到拼音的映射。

  create   function   f_GetPy(@Str   nvarchar(400))
  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   @t   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

调用方法
print dbo.f_GetPy('打算送到根深蒂固')

转载于:https://www.cnblogs.com/wangpei/archive/2010/01/11/1644284.html