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

1
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

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.

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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