SQL SERVER 得到汉字首字母函数四版全集 --【叶子】

formatuu

--创建取汉字首字母函数(第三版)create function [dbo].[f_getpy_V3] (@col varchar(1000))returns varchar(1000)as begindeclare @cyc int,@len int,@sql varchar(1000),@char varbinary(20)select @cyc = 1,@len = len(@col),@sql = ''while @cyc <= @len begin  select @char = cast(substring(@col, @cyc, 1) as varbinary)declare @maco table (bcode varbinary(20),ecode varbinary(20),letter varchar(10))insert into @macoselect 0XB0A1,0XB0C4,'A' union allselect 0XB0C5,0XB2C0,'B' union allselect 0XB2C1,0XB4ED,'C' union allselect 0XB4EE,0XB6E9,'D' union allselect 0XB6EA,0XB7A1,'E' union allselect 0XB7A2,0XB8C0,'F' union allselect 0XB8C1,0XB9FD,'G' union allselect 0XB9FE,0XBBF6,'H' union allselect 0XBBF7,0XBFA5,'J' union allselect 0XBFA6,0XC0AB,'K' union allselect 0XC0AC,0XC2E7,'L' union allselect 0XC2E8,0XC4C2,'M' union allselect 0XC4C3,0XC5B5,'N' union allselect 0XC5B6,0XC5BD,'O' union allselect 0XC5BE,0XC6D9,'P' union allselect 0XC6DA,0XC8BA,'Q' union allselect 0XC8BB,0XC8F5,'R' union allselect 0XC8F6,0XCBF9,'S' union allselect 0XCBFA,0XCDD9,'T' union allselect 0XCDDA,0XCEF3,'W' union allselect 0XCEF4,0XD1B8,'X' union allselect 0XD1B9,0XD4D0,'Y' union allselect 0XD4D1,0XD7F9,'Z'select top 1 @sql=@sql+letter from @maco where @char between bcode and ecode set @cyc = @cyc + 1 endreturn @sqlendgo--创建取汉字首字母函数(第四版)create function [dbo].[f_getpy_V4](@col varchar(1000))returns varchar(1000)begindeclare @cyc int,@len int,@sql varchar(1000),@char varbinary(20)select @cyc = 1,@len = len(@col),@sql = ''while @cyc <= @len begin  select @char = cast(substring(@col, @cyc, 1) as varbinary)if @char>=0XB0A1 and @char<=0XB0C4      set @sql=@sql+'A'else if @char>=0XB0C5 and @char<=0XB2C0 set @sql=@sql+'B'else if @char>=0XB2C1 and @char<=0XB4ED set @sql=@sql+'C'else if @char>=0XB4EE and @char<=0XB6E9 set @sql=@sql+'D'else if @char>=0XB6EA and @char<=0XB7A1 set @sql=@sql+'E'else if @char>=0XB7A2 and @char<=0XB8C0 set @sql=@sql+'F'else if @char>=0XB8C1 and @char<=0XB9FD set @sql=@sql+'G'else if @char>=0XB9FE and @char<=0XBBF6 set @sql=@sql+'H'else if @char>=0XBBF7 and @char<=0XBFA5 set @sql=@sql+'J'else if @char>=0XBFA6 and @char<=0XC0AB set @sql=@sql+'K'else if @char>=0XC0AC and @char<=0XC2E7 set @sql=@sql+'L'else if @char>=0XC2E8 and @char<=0XC4C2 set @sql=@sql+'M'else if @char>=0XC4C3 and @char<=0XC5B5 set @sql=@sql+'N'else if @char>=0XC5B6 and @char<=0XC5BD set @sql=@sql+'O'else if @char>=0XC5BE and @char<=0XC6D9 set @sql=@sql+'P'else if @char>=0XC6DA and @char<=0XC8BA set @sql=@sql+'Q'else if @char>=0XC8BB and @char<=0XC8F5 set @sql=@sql+'R'else if @char>=0XC8F6 and @char<=0XCBF9 set @sql=@sql+'S'else if @char>=0XCBFA and @char<=0XCDD9 set @sql=@sql+'T'else if @char>=0XCDDA and @char<=0XCEF3 set @sql=@sql+'W'else if @char>=0XCEF4 and @char<=0XD1B8 set @sql=@sql+'X'else if @char>=0XD1B9 and @char<=0XD4D0 set @sql=@sql+'Y'else if @char>=0XD4D1 and @char<=0XD7F9 set @sql=@sql+'Z'set @cyc = @cyc + 1 endreturn @sqlendgo--创建取汉字首字母函数(第一版)create function [dbo].[f_getpy_V1] (@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @word nchar(1),@py nvarchar(4000)set @py=''while len(@str)>0beginset @word=left(@str,1)set @py = @py+ (case when unicode(@word) between 19968 and 19968+20901then (select top 1 pyfrom(select 'a' as py, N'驁' as wordunion 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'咗') Twhere word>=@word collate Chinese_PRC_CS_AS_KS_WSorder by py asc)else @wordend)set @str=right(@str,len(@str)-1)endreturn @PYendgo--创建取汉字首字母函数(第二版)create function [dbo].[f_getpy_V2](@Str varchar(500)='')returns varchar(500)asbegindeclare @strlen int,@return varchar(500),@ii intdeclare @n int,@c char(1),@chn nchar(1)select @strlen=len(@str),@return='',@ii=0set @ii=0while @ii<@strlenbeginselect @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 endfrom(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 aorder by chn COLLATE Chinese_PRC_CI_AS) as belse set @c='a'set @return=@return+@cendreturn(@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个函数哪个最高效呢？可以把开销百分比留言在下面，谢谢！