Splitting strings with auxiliary table of numbers

Couple of posts back I was showing how to combine several tags into a single string, separated with spaces.

Today, my dear Reader, I am going to show you how to split them back into the set of rows.

First, we are going to create an auxiliary table of numbers.
This is an extremely useful thing by itself, which I learned from the book of Itzik Ben-Gan: Inside Microsoft® SQL Server® 2008: T-SQL Programming, Microsoft Press, 2010.

IF OBJECT_ID (N'dbo.Numbers', N'U') IS NOT NULL

        DROP TABLE dbo.Numbers;

GO

CREATE TABLE dbo.Numbers

(

        N INT NOT NULL PRIMARY KEY

)

GO

SET NOCOUNT ON;

DECLARE @max INT = 1000

                , @rc INT = 1;

INSERT INTO dbo.Numbers VALUES(1);

WHILE @rc * 2 <= @max

BEGIN

        INSERT INTO dbo.Numbers

        SELECT n + @rc FROM dbo.Numbers;

       

        SET @rc = @rc * 2;

END

INSERT INTO dbo.Numbers

SELECT n + @rc FROM dbo.Numbers WHERE n + @rc <= @max;

GO

Then we do the trick:

IF OBJECT_ID('dbo.Split', 'IF') IS NOT NULL

DROP FUNCTION dbo.Split;

GO

CREATE FUNCTION dbo.Split (

        @string NVARCHAR(1000)

        , @separator NVARCHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

        WITH separated AS

        (

                SELECT

                        SUBSTRING(@separator + @string, n, LEN(@separator + @string)) s

                FROM dbo.Numbers

                WHERE

                        n <= LEN(@separator + @string)

                        AND SUBSTRING(@separator + @string, n, 1) = @separator

        )

        , cut AS

        (

                SELECT

                        LTRIM(SUBSTRING (s, 1, CHARINDEX(@separator, s + @separator, 2) - 1)) s

                FROM separated

        )

        , filtered (tag) AS

        (

                SELECT s

                FROM cut

                WHERE LEN(s) > 0

        )

        SELECT tag from filtered

GO