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

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

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

0 ·

CC 4.0 BY-SA版权

文章标签：

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

SQL Server/ACCESS 专栏收录该内容

3 篇文章

订阅专栏

博客展示了在SQL Server中创建自定义拼音函数f_GetPy的代码，通过插入拼音对应关系数据，利用循环和条件判断实现汉字转拼音功能，最后给出了函数的调用示例，用于筛选符合拼音条件的数据。

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'