ALTER FUNCTION [dbo].[GetPy](@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)
if @chn>'z'
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
else set @c=@chn
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),@i int
set @PY=''
set @i=1
while (substring(@str,@i,1)<>'' or @i<=len(@str))
begin
set @word=substring(@str,@i,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 @i=@i+1
end
return @PY
end
--函数调用实例:
select dbo.fun_getPY('中华人民共和国'),dbo.fun_getPY('中華人民共和國')
结果都为:ZHRMGHG