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
