Back to all posts

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

If we have one series with comma separate(any Delimiter) value and we want split into in rows, I have create function for that: CREATE OR ALTER FUNCTION [dbo...

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
*/

0 likes

Rate this post

No rating

Tap a star to rate

0 comments

Latest comments

0 comments

No comments yet.

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.