Split delimited string into rows

tangled string

On several occasions I’ve been assigned the task to split 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 delimited string into rows and want to share?

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

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 *

en_USEnglish