How to one series with comma separate(any Delimiter) value split into in rows

Lesson 29 of 96

If we have one series with comma separate(any Delimiter) value and we want split into in rows, I have create function for that:

SQL
CREATE OR ALTER FUNCTION [dbo].[TF_SplitSeriesIntoRow]
(
    @text      NVARCHAR(MAX),
    @delimiter NVARCHAR(20) = ','
)
RETURNS TABLE
AS
RETURN
(
    WITH
    -- Virtual numbers table banao — koi physical table nahi chahiye
    E1(N) AS
    (
        SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),
                             (1),(1),(1),(1),(1)) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),   --       100 rows
    E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),   --    10,000 rows
    E6(N) AS (SELECT 1 FROM E2 a CROSS JOIN E4 b),   -- 1,000,000 rows

    -- TOP se sirf utne hi rows lenge jitne string ki length hai
    Tally(N) AS
    (
        SELECT TOP (ISNULL(LEN(@text), 0) + 1)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM E6
    ),

    -- Har token ki start position dhundho
    TokenStarts(start_pos) AS
    (
        SELECT 1
        WHERE  @text IS NOT NULL AND LEN(@text) > 0

        UNION ALL

        SELECT N + LEN(@delimiter)
        FROM   Tally
        WHERE  @text IS NOT NULL
          AND  LEN(@delimiter) > 0
          AND  N <= LEN(@text) - LEN(@delimiter) + 1
          AND  SUBSTRING(@text, N, LEN(@delimiter)) = @delimiter
    )

    -- Start position se token extract karo
    SELECT
        SUBSTRING(
            @text,
            start_pos,
            ISNULL(NULLIF(CHARINDEX(@delimiter, @text, start_pos), 0),
                   LEN(@text) + 1) - start_pos
        ) AS value
    FROM TokenStarts
);
GO

-- USE KARO — CSV string ko rows mein convert karo:
SELECT s.value AS SkillName
FROM dbo.[TF_SplitSeriesIntoRow]('SQL Server,,Python,Power BI,Azure', ',') s;
/*
Result:
SQL Server

Python
Power BI
Azure
*/