and @char<=0XD7F9 set @sql=@sql+'Z'
set @cyc = @cyc + 1
end
return @sql
end
go
--创建取汉字首字母函数(第一版)
create function [dbo].[f_getpy_V1] (@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@py nvarchar(4000)
set @py=''
while len(@str)>0
begin
set @word=left(@str,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 @str=right(@str,len(@str)-1)
end
return @PY
end
go
--创建取汉字首字母函数(第二版)
create function [dbo].[f_getpy_V2](@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='a'
set @return=@return+@c
end
return(@return)
end
--思路基本是一样的，但是不同类型导致效率上有差别，这个差别不同环境测试出来的效果竟然不一样。
select dbo.f_getpy_V1('我是一个土生土长的中国人')
select dbo.f_getpy_V2('我是一个土生土长的中国人')
select dbo.f_getpy_V3('我是一个土生土长的中国人')
select dbo.f_getpy_V4('我是一个土生土长的中国人')
--我现在测试到的开销百分比是：
--1:2:3:4 对应18%:38%:44%:0%
--如果你感兴趣也可以在本地测试一下，看看执行计划，这4个函数哪个最高效呢？可以把开销百分比留言在下面，谢谢
摘自 沧海一叶点滴心得
|