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
DECLARE @test VARCHAR(100);
SET @test = 'car,dog,cat,,house';

-- Required variables
DECLARE @delim   VARCHAR(1)
       ,@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

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

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

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.