字符串的Split,是实际项目中经常遇到的。这一次的项目中,就有一个早已援用至今的共通函数。输入的参数是被处理的字符串和分割符,返回值是存放分割后的子串的Table。这个共通函数里是用循环的方式一个一个的取出子串。
今天偶然看见一段代码,很巧妙地用一个SQL语句(Insert+Select)就实现了Split,只不过它是固定长度划分。参照它的思想,我实现了按分割符划分,也是一个SQL语句。
原参照代码:固定长度Split(http://sqlservernation.com/blogs/tipweek/archive/2009/04/02/Fast-searching-of-large-_2800_n_2900_varchar-values.aspx) create table [util_Nums_361A370A-D881-4612-BE1C-916BD466E884] (n int not null ,constraint [pk_util_Nums_361A370A-D881-4612-BE1C-916BD466E884] primary key clustered (n) ) go ;with cte0 as (select 1 as c union all select 1), cte1 as (select 1 as c from cte0 a, cte0 b), cte2 as (select 1 as c from cte1 a, cte1 b), cte3 as (select 1 as c from cte2 a, cte2 b), cte4 as (select 1 as c from cte3 a, cte3 b), cte5 as (select 1 as c from cte4 a, cte4 b), nums as (select top 1000000 row_number() over (order by c) as n from cte5) insert into [util_Nums_361A370A-D881-4612-BE1C-916BD466E884](n) select n from nums create function dbo.[fn_SplitEveryX_361A370A-D881-4612-BE1C-916BD466E884] (@InputString nvarchar(max) ,@Every int ) returns @Data table (ident int identity ,theData nvarchar(max) ) as begin insert into @Data (theData) select substring(@inputstring, n , @every) from dbo.[util_Nums_361A370A-D881-4612-BE1C-916BD466E884] where n <= (datalength(@Inputstring)/2) and (n-1)%@every=0 return end
我的分割符Split代码,如下: declare @inputstring nvarchar(max)=N'12##34567##890##123' , -- 待分割字符串 @spt nvarchar(max)=N'##', -- 分割符 @len_spt int SET @len_spt = LEN(@spt) ;with tb1(ss, pos, id) as ( select substring(@inputstring, 1 , case when substring(@inputstring,n ,@len_spt)=@spt then n-1 else n end) ,n , ROW_NUMBER() over (order by n) from dbo.[util_Nums_361A370A-D881-4612-BE1C-916BD466E884] where (n < (datalength(@inputstring)/2) and substring(@inputstring,n ,@len_spt)=@spt) or (n =(datalength(@inputstring)/2)) ) select SUBSTRING(a.ss, case a.id WHEN 1 then 1 else b.pos+@len_spt end,a.pos-ISNULL(b.pos,0)) FROM tb1 a LEFT join tb1 b on a.id=b.id + 1
测试通过,特此纪录。 |