Skip to content

Using Comma Delimited Lists in SQL Server

September 1, 2015

Have you ever wanted to take a list of values that are delimited by a comma (or any other character) and find records that match the individual items in your list? It doesn’t work very well at all. I have a quick and easy way to do it using a user defined function.

It is fairly easy to deal with XML in SQL Server these days so if we can convert the delimited list into XML we can easily insert the XML into a table and then join on it.

So all our function needs to do is accept a list, a delimiter (we will make it default to a comma). Then it can convert the list into XML, insert it into the table that the function returns and we have our solution:

create function [dbo].[fnIterativeListToTable] (
@list nvarchar(max),
@delimiter nchar(1) = N’,’)
returns @tbl table (listpos int identity(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000))
as

begin
declare @xml xml

select @xml = CAST(‘<Item>’+ REPLACE(@list,@delimiter,'</Item><Item>’)+ ‘</Item>’ as xml)
insert into @tbl
select t.value(‘.’, ‘varchar(4000)’) as str,
t.value(‘.’, ‘nvarchar(2000)’) as nstr
from @xml.nodes(‘/Item’) as x(t)
return
end

Now, let’s see how we can use this function. First, let’s create a new table and insert some data into it so we can use it test our new function (I randomly generated a few names for us to use):

create table SampleData(Id int identity(1,1),Value varchar(50))

insert into SampleData(Value) values
(‘Mel’),
(‘Jeannine’),
(‘Francie’),
(‘Melisa’),
(‘Terry’),
(‘Wendi’),
(‘Angelic’),
(‘Natividad’),
(‘Loma’),
(‘Tajuana’),
(‘Dana’),
(‘Ellena’),
(‘Mercy’),
(‘Luke’),
(‘Errol’),
(‘Alejandrina’),
(‘Verdie’),
(‘Melissia’),
(‘Cythia’),
(‘Kemberly’)

Now lets build a comma delimited list that includes a few names in the table:

declare @list varchar(100)
set @list = ‘Wendi,Luke,Mel,Cythia’

Finally, all we need to do is join our new table to our function and pass in the list:

select s.*
from SampleData s
inner join dbo.fnIterativeListToTableXml(@list, default) l
on s.Value = l.str

If we run our query we will get the records for only the names that are in our list. Alternatively, you could also write the same query using a where statement:

select *
from SampleData s
where Value in (select str from dbo.fnIterativeListToTableXml(@list, default))

There you have it. Let me know if you have any thoughts or questions about this process. Especially let me know if you have a better way to handle delimited lists in SQL server.

Jeff

statusnotquo.com

Advertisements
No comments yet

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: