RiCo技術農場

利用CTE不用回圈寫一個 table function達到split效果

create function [dbo].[myStrSplit]
(
    @inputcontent nvarchar(max),
    @schar char(1)
)
RETURNS TABLE 
as
return
(
with mysplie(i,j)
as
(
select i=1, j= cast(charindex(@schar,@inputcontent) as int)
union all
select i=j+1
,j=
case
 when cast(charindex(@schar,@inputcontent,j+1) as int) >0 
    then cast(charindex(@schar,@inputcontent,j+1) as int)
 else cast(LEN(@inputcontent)+1 as int)
end 
from mysplie
where charindex(@schar,@inputcontent,j+1) > 0 
or LEN(@inputcontent)-(i+1)>0

)

 SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
 'data'=substring(@inputcontent,i,j-i)
        --'data' = SUBSTRING(@inputcontent,i,COALESCE(NULLIF(j,0),LEN(@inputcontent)+1)-i)
    FROM mysplie
     where i<j
)

測試

select * from [dbo].[myStrSplit]('\abc\rico\文件\','\')
select * from [dbo].[myStrSplit]('abc\rico\文件\','\')
select * from [dbo].[myStrSplit]('\abc\rico\文件','\')
select * from [dbo].[myStrSplit]('abc\rico\文件','\')