字符串的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

测试通过，特此纪录。