字符串的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 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 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
测试通过，特此纪录。
