sql server中用到的函数，用来取拼音

最新推荐文章于 2024-03-27 18:37:22 发布

原创最新推荐文章于 2024-03-27 18:37:22 发布 · 779 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #insert #function #table #go #c

MSSQL 分类专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个 SQL Server 中用于从输入字符串提取拼音的自定义函数 f_GetPy。该函数通过匹配输入字符串中的汉字并返回对应的拼音首字母来实现功能。提供了完整的 T-SQL 代码示例及如何调用此函数的方法。

一个sql server中用到的函数，用来取拼音

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 alls where dbo.f_GetPy(name)='cyml'