字符串的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
測試通過，特此紀錄。
