sql 创建取拼音函数

最新推荐文章于 2023-04-08 23:12:48 发布

转载最新推荐文章于 2023-04-08 23:12:48 发布 · 66 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zgaspnet/archive/2011/07/01/2095644.html

本文介绍了一个使用T-SQL编写的函数f_GetPy，该函数能够将输入的中文字符串转换为对应的汉语拼音。通过定义一张拼音对照表并逐字符匹配转换，实现了中文到拼音的基本功能。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--创建取拼音函数
ALTER function [dbo].[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

转载于:https://www.cnblogs.com/zgaspnet/archive/2011/07/01/2095644.html