SQL Server 2005 - Split string into table using WITH recursion (no loops)

[CVMichael]

This code is faster than using loops

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20091104>
-- Description: <Split string into table, separator can be more than 1 char>
-- =============================================
CREATE FUNCTION [dbo].[fn_SplitStringToTable]
(
      @DataList NVARCHAR(MAX)
    , @Separator NVARCHAR(MAX)
)
RETURNS @tbl TABLE (
      RowIndex INT PRIMARY KEY
    , FromPos INT
    , ToPos INT
    , ItemData NVARCHAR(MAX)
)
AS
BEGIN
    -- SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231,111', ',')
    
    DECLARE @LenSep INT
    SET @LenSep = DATALENGTH(@Separator) / 2
    
    IF @LenSep > 0 AND DATALENGTH(@DataList) > 0 BEGIN
        ; WITH res (RowIndex, FromPos, ToPos) AS (
            SELECT CAST(1 AS INT) AS RowIndex
                , CAST(1 AS INT) AS FromPos
                , CAST(CHARINDEX(@Separator, @DataList + @Separator) AS INT) AS ToPos
            
            UNION ALL
            
            SELECT CAST(RowIndex + 1 AS INT) AS RowIndex
                , CAST(res.ToPos + @LenSep AS INT) AS FromPos
                , CAST(CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) AS INT) AS ToPos
            FROM res
            WHERE CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) > 0
        )
        INSERT INTO @tbl
        SELECT res.*, SUBSTRING(@DataList, FromPos, ToPos - FromPos) AS ItemData
        FROM res
        OPTION (MAXRECURSION 0)
    END
    
    RETURN
END

[techgnome]

recursive CTEs are so awesome.... I'll be adding this to my list of tricks.

-tg

[halford13]

check this discussion: http://www.sqlservercentral.com/Foru...39-1683-1.aspx

[si_the_geek]

It appears that you have posted an invalid link, perhaps the discussion (about what?) was deleted.

[halford13]

hmm ... the link works for me.
Lets try again:
http://www.sqlservercentral.com/Foru...39-1683-1.aspx

"http://www.sqlservercentral.com/Forums/Topic817039-1683-1.aspx"

[si_the_geek]

It still doesn't work for me I'm afraid - it just redirects to their main page (I can open other threads there using similar URLs).

[CVMichael]

si_the_geek, that's because you have to register to that forum, and be logged-in when you click on the link.

It does not work for me either when I'm not logged-in