Split Function in SQL Server

Hello everyone,

In this article, I will try to give information about the Split function in SQL Server.

In SQL Server, in some cases, you may want to show the comma strings you have in a tabular form by separating them with Id.

You can easily do this using the code below.

CREATE FUNCTION fn_Split
(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
)
RETURNS @t TABLE
(
    Id INT IDENTITY(1, 1),
    Val NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @xml XML;
    SET @xml = N'<root><r>' + REPLACE(@delimited, @delimiter, '</r><r>') + '</r></root>';

    INSERT INTO @t
    (
        Val
    )
    SELECT r.value('.', 'varchar(max)') AS item
    FROM @xml.nodes('//root/r') AS records(r);

    RETURN;
END;
GO

--Using the Function

SELECT * FROM dbo.fn_Split('2,3,6,5,8,9,99',',')

As you can see, we have separated the comma strings with Id.

Good luck to everyone in business and life.