Another way to split strings in SQL (pre 2016)

A while back, I posted this entry on how to loop through a delimited string in SQL.

But what if you wanted to use the items in the list as a set in a query?

I don’t know why I didn’t think of it sooner, but the following works well:

-- The list to be parsed
SET @test = 'car,dog,cat,,house';

-- Required variables
       ,@execstr VARCHAR(MAX);
SET @delim = ',';
SET @execstr = 'SELECT ''' +
               REPLACE(@test,@delim,''' AS [item] UNION SELECT ''')+''' AS [item];';

If you just want to see the items in the list:

EXEC (@execstr);

This will return

2 car
3 cat
4 dog
5 house

If you want to use the items in the list in subsequent queries:

DECLARE @items TABLE ( [item] VARCHAR(10) );
INSERT INTO @items EXEC (@execstr);

Now use the table to access the data. For example:

SELECT * FROM @items WHERE [item] like '%o%';

This will return

1 dog
2 house

Another technique that can be used is to convert the list to an XML element:

DECLARE @array XML = CAST('<item>' + REPLACE(@test, ',', '</item><item>') + '</item>' AS XML);

SELECT arr.item.value('.', 'NVARCHAR(10)')
  FROM @array.nodes('//item') arr(item);

This will return

1 car
2 dog
3 cat
5 house

Notice that this method also has the advantage of preserving the order of the items when selected.

This will be a lot easier under 2016 when I can use the STRING_SPLIT function. But for now, while I’m stuck in 2014, this works for me.


One thought on “Another way to split strings in SQL (pre 2016)”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s