• I got it right, by focusing on the WHERE clause (that alone dictates the number of rows), and hoping that there was no parentheses mismatch or similar error buried in the code.

    The code seems overly complicated to me. While I agree that it is "one of several examples of practical use of Tally table.", I would much rather see a GOOD example of practical use of a Tally table.

    Here is a much simpler and easier to understand way to get the same results. Note that I adapted this code from a snippet taken from Erland Sommarskog's website.

    DECLARE @Text NVARCHAR(500);

    DECLARE @StringDelimiter CHAR(1);

    SET @Text = 'This T-SQL will split these sentences into rows.' +

    'How many rows will be returned?.' +

    'M.a.y.b.e..n.o.n.e.?';

    SET @StringDelimiter = '.';

    -- Add delimiter before and after text;

    -- This removes the need for special code to handle start and end of string.

    DECLARE @TextPlus NVARCHAR(502)

    SET @TextPlus = @StringDelimiter + @Text + @StringDelimiter;

    WITH Tally(Number) AS

    (SELECT 1 AS Number

    UNION ALL

    SELECT Number + 1 AS Number

    FROM Tally

    WHERE Number < LEN(@TextPlus))

    SELECT SUBSTRING(@TextPlus,

    Number + 1,

    CHARINDEX(@StringDelimiter, @TextPlus, Number + 1) - Number - 1) AS SPLIT

    FROM Tally

    WHERE Number <= LEN(@TextPlus) - 1

    AND SUBSTRING(@TextPlus, Number, 1) = @StringDelimiter

    ORDER BY Number

    OPTION (MAXRECURSION 0);


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/