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