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

If we have two series with comma separate(any Delimiter) value and we want split into in rows, I have create function for that: IF OBJECT_ID('TF_SplitTwoSe…

Author

SQLDataDev Editorial Team

Mar 19, 2026 1 min read

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

IF OBJECT_ID('TF_SplitTwoSeriesIntoRows','TF') IS NOT NULL 
BEGIN
    DROP FUNCTION &#091;dbo].&#091;TF_SplitTwoSeriesIntoRows]
END
GO

CREATE FUNCTION &#091;dbo].&#091;TF_SplitTwoSeriesIntoRows] (@Series1 VARCHAR(MAX), @Series2 VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @SplitValues TABLE (Value1 VARCHAR(MAX), Value2 VARCHAR(MAX))
AS
BEGIN
    DECLARE @Value1 VARCHAR(MAX)
    DECLARE @Value2 VARCHAR(MAX)
    DECLARE @Pos INT

    WHILE CHARINDEX(@Delimiter, @Series1) > 0 AND CHARINDEX(@Delimiter, @Series2) > 0
    BEGIN
        SET @Pos = CHARINDEX(@Delimiter, @Series1)
        SET @Value1 = SUBSTRING(@Series1, 1, @Pos - 1)
        SET @Series1 = SUBSTRING(@Series1, @Pos + 1, LEN(@Series1) - @Pos)

        SET @Pos = CHARINDEX(@Delimiter, @Series2)
        SET @Value2 = SUBSTRING(@Series2, 1, @Pos - 1)
        SET @Series2 = SUBSTRING(@Series2, @Pos + 1, LEN(@Series2) - @Pos)

        INSERT INTO @SplitValues (Value1, Value2) VALUES (@Value1, @Value2)
    END

    -- Insert the remaining values (last values)
    INSERT INTO @SplitValues (Value1, Value2) VALUES (@Series1, @Series2)

    RETURN
END
GO

SELECT * FROM TF_SplitTwoSeriesIntoRows('A,2,1','6,MM,4',',')

Topics covered

Keep building your data skillset

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

Browse All Posts