sqlserver 取汉字拼音码首字母

最新推荐文章于 2023-09-02 15:24:46 发布

sdrzths

最新推荐文章于 2023-09-02 15:24:46 发布

阅读量849

点赞数

本文链接：https://blog.csdn.net/sdrzths/article/details/4238631

版权

create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
set @return=@return+@c
end
return(@return)
end

create   function   fun_getPY(@str   nvarchar(4000))
  returns   nvarchar(4000)
  as
  begin
  declare   @word   nchar(1),@PY   nvarchar(4000)
  set   @PY=''
  while   len(@str)>0
  begin
  set   @word=left(@str,1)
  --如果非汉字字符，返回原字符
  set   @PY=@PY+(case   when   unicode(@word)   between   19968   and   19968+20901
  then   (select   top   1   PY   from   (
  select   'A'   as   PY,N'驁'   as   word
  union   all   select   'B',N'簿'
  union   all   select   'C',N'錯'
  union   all   select   'D',N'鵽'
  union   all   select   'E',N'樲'
  union   all   select   'F',N'鰒'
  union   all   select   'G',N'腂'
  union   all   select   'H',N'夻'
  union   all   select   'J',N'攈'
  union   all   select   'K',N'穒'
  union   all   select   'L',N'鱳'
  union   all   select   'M',N'旀'
  union   all   select   'N',N'桛'
  union   all   select   'O',N'漚'
  union   all   select   'P',N'曝'
  union   all   select   'Q',N'囕'
  union   all   select   'R',N'鶸'
  union   all   select   'S',N'蜶'
  union   all   select   'T',N'籜'
  union   all   select   'W',N'鶩'
  union   all   select   'X',N'鑂'
  union   all   select   'Y',N'韻'
  union   all   select   'Z',N'咗'
  )   T
  where   word>=@word   collate   Chinese_PRC_CS_AS_KS_WS
  order   by   PY   ASC)   else   @word   end)
  set   @str=right(@str,len(@str)-1)
  end
  return   @PY
  end