// Split delimited string into rows

On several occasions I’ve been assigned the task to split a delimited string into rows.

I’ve done this in different ways, but never thought about the performance or stability of the different approaches for doing this.

So here’s my 25 cents and findings.

My first solution to this was to code a function to traverse through the string and insert a new value to a temp table for every delimiter found in the string:

CREATE FUNCTION [dbo].[list_to_table] (
            @list varchar(4000)
            )

RETURNS @tab TABLE (
            item int
            )

BEGIN

IF CHARINDEX(',',@list) = 0 or CHARINDEX(',',@list) is null
BEGIN
    INSERT INTO @tab (item) VALUES (@list);
    RETURN;
END

DECLARE @c_pos INT;
DECLARE @n_pos INT;
DECLARE @l_pos INt;

SET @c_pos = 0;
SET @n_pos = CHARINDEX(',',@list,@c_pos);

WHILE @n_pos < 0
BEGIN
    INSERT INTO @tab (item) VALUES (CAST(SUBSTRING(@list,@c_pos+1,@n_pos - @c_pos-1) as int));
    SET @c_pos = @n_pos;
    SET @l_pos = @n_pos;
    SET @n_pos = CHARINDEX(',',@list,@c_pos+1);
END;

INSERT INTO @tab (item) VALUES (CAST(SUBSTRING(@list,@l_pos+1,4000) as int));

RETURN;
END

Then I ran into performance issues with very long strings - pushing me to find a better solution with more performance. I began to look into the xml-aproach for solving the issue - and ended up with this:

declare @string as nvarchar(4000)
select 
	r.value('@value','int') as Kategori
from (
	select cast(<A value = "'+ replace(@string,',','"/<>;A value = "')+ '">' as xml
	) as xml_str) xml_cte 
cross apply xml_str.nodes('/A') as x(r)

Performance for the two different solutions is shown below:

String contains all numbers from 0 to 100 with comma as delimiter, machine 4 cores 16 gb ram and ssd.

Function: 7 ms (on average)
XML: 3 ms (on average)

No matter how long a string I send to the XML code it runs around 3 ms - the function just climbs and climbs in time (naturally).

Anybody who has done the same and found an even better way to dynamically split a string into rows and want to share?