SQL生成开头字母

最新推荐文章于 2023-02-22 11:16:24 发布

转载最新推荐文章于 2023-02-22 11:16:24 发布 · 967 阅读

文章标签：

#sql #insert #function #table #c

SQLSERVER 专栏收录该内容

32 篇文章

订阅专栏

 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

  go



--调用

select dbo.f_GetPy(N'中国人') result



/*

result

-----------

ZGR

*/