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?.' +
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
SELECT Number + 1 AS Number
WHERE Number < LEN(@TextPlus))
Number + 1,
CHARINDEX(@StringDelimiter, @TextPlus, Number + 1) - Number - 1) AS SPLIT
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: http://sqlblog.com/blogs/hugo_kornelis