In this article, I will show you how you can convert a delimited string of data into a table of data.  In order to do this we will use a table valued function.  There are many ways to achieve this but this how I like to do it. 

In order to use the function, you will need a numbers table.  Since that's a topic that we haven't covered here yet, we will utilize a view that builds one on the fly.  The view can be found in the A View of Numbers post.  If you have a numbers table, replace "vw_nums" with the name of the table in the below function and replace "n" with the column name that contains your numbers.

Run the following SQL to create the function.

create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
begin

insert into @Values (thePosition,theValue)
select n
  ,substring(@delimiter + @DelimitedString + @delimiter
            ,n + (datalength(@delimiter)/2)
            ,charindex(@delimiter, @delimiter + @DelimitedString + @delimiter
                      ,n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2
                      ) as string_value
from    dbo.vw_Nums
where
    n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter


return
end

GO

This function works by utilizing the numbers table to represent the characters in the string.  This allows SQL Server to quickly find the @Delimiter in the string without doing any sort of while loops or cursors.  It accepts a @DelimitedString and a @delimiter as input, and outputs three values.  The @DelimitedString input parameter is a nvarchar(max) input parameter, but the actual string to be parsed can be no longer than the number of values in your numbers table.The "ident" field is an identity field that represents the position of the parsed string within the @DelimitedString.  The field "thePosition" represents the starting position of the value within the @DelimitedString.  The field "theValue" is the value within the @DelimitedString that falls between two instances of the @Delimiter.

Sample usage:
This will show you the basics of how this function works and what you can expect to see.

select * from [dbo].[fn_DelimitedToTable]('Jim,John,Mike,Bob,Tom',',')

As you can see, it returns the five names in the string, in the order they appeared.  This script is useful when an application has to pass in a delimited list of values to search for.  By splitting the values into a table it can be joined to for selecting multiple values.

This example shows that you can use a delimiter greater than one character.  This is useful when your data can contain any character and there is no ONE character that you can use to delimit the string.

select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')

Now let's talk about some possible uses for this type of functionality. 

Let's say that your application needs to search and return a list of employees.  There are many ways to pass in the parameters.  One of which is XML.  But SQL Server isn't the most efficient as processing XML so for this case we will use a delimited string.  The first thing we need to do is setup our test table

Note: If the table name looks strange, I use a partial newid() value to make the table unique.  Although we recommend that you never test on a production server, or even in a used database on a development server, we try to make the names unique to prevent conflicting with your existing tables.

create table dbo.employees_FB8AD58DADAF
     (userid        int identity primary key clustered
     ,firstname     nvarchar(50) not null
     ,lastname      nvarchar(50) not null
     )
GO
insert into dbo.employees_FB8AD58DADAF(firstname,lastname)
          select 'Jim', 'Smith'
union all select 'John', 'Smith'
union all select 'Mike', 'Smith'
union all select 'Bob', 'Smith'
union all select 'Tom', 'Smith'
union all select 'Tim', 'Smith'
union all select 'Rob', 'Smith'
union all select 'Matt', 'Smith'

Now let's see how we can use the above function to search.  The below procedure accepts a @userlist parameter.  The @userlist parameter is a comma delimited list of user first names to search for in the table we created.  When we create and run the below procedure

create procedure dbo.up_employeesearch
      @UserList   nvarchar(max)
as
set nocount on
select e.userid,e.firstname,e.lastname from dbo.employees_FB8AD58DADAF e
  join [dbo].[fn_DelimitedToTable](@userlist,N',') t
    on e.firstname = t.thevalue
go
exec dbo.up_employeesearch @userlist = 'Jim,John,Mike,Bob,Tom'

We get the following results:

There are a lot of uses for this type of functionality and I'm sure you can already think of a few.  Just remember that before you start making too much use of this, you should consider converting it to use a numbers table instead of the vw_nums view.