Split delimited string into rows

Posted on Posted in Database, Development, Performance, T-SQL

On several occasions I’ve been assigned the task to split a delimited stringtangled 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?

3 thoughts on “Split delimited string into rows

  1. Perhaps it’s too late for comment but
    1. Perhaps the fastest way is using SQLCLR
    2. In may case I also stopped on XML as good enough (fast and flexible) solution and cuz my case was a little more complex then just spit.

    Just a small thing – element in many cases much faster then attribute
    Just as example

    DECLARE @StrToSplit nvarchar(MAX) =
    STUFF( (SELECT N’,’, name AS “text()” FROM sys.objects
    FOR XML PATH(”)),1,1,”)

    select
    r.value(‘@value’,’NVARCHAR(128)’) as Kategori
    from (
    select cast(‘‘ as xml
    ) as xml_str) xml_cte
    cross apply xml_str.nodes(‘/A’) as x(r)

    DECLARE @Str as xml
    SELECT @Str = CAST(N”+REPLACE(@StrToSplit,’,’,”)+” AS XML)

    SELECT T.c.value(‘(.)[1]’,’nvarchar(128)’)
    FROM
    @Str.nodes(‘/root/field’) AS T(c)

Leave a Reply

Your email address will not be published. Required fields are marked *