Delimited String Parsing in SQL Server 2005 and later

This article will describe one method to parse a delimited string into a table of data.  

Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we want to return a list of items with their details, given a list of item numbers.

1. Create the view the function depends on

Execute the following code to create the vw_Nums view.  You will need to run it in SQL Server Management Studio's Query tool, not in the query builder.  I use a view to generate a list of 4 billion numbers.   But if your database has a numbers table, you can use it as well.  Just update vw_Nums in the function with your table name and "n" (without quotes) with your column name.  Just be sure that you have enough numbers.  There has to be at least as many numbers as characters in your input string.  The view's code is also in a comment in the function so that if it gets dropped, it can be recreated by copying the text out of the function.

create view vw_Nums
                      as
                      with   cte0 as (select 1 as c union all select 1), 
                             cte1 as (select 1 as c from cte0 a, cte0 b), 
                             cte2 as (select 1 as c from cte1 a, cte1 b), 
                             cte3 as (select 1 as c from cte2 a, cte2 b), 
                             cte4 as (select 1 as c from cte3 a, cte3 b), 
                             cte5 as (select 1 as c from cte4 a, cte4 b), 
                             nums as (select row_number() over (order by c) as n from cte5)
                             select n from nums 

Select all Open in new window

2. Create the function

The below code snippet will create the function.

if object_id('[dbo].[fn_DelimitedToTable]') is not null
                           drop function [dbo].[fn_DelimitedToTable]
                      go
                      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
                      /************************************************************
                      *
                      *    Author:        Brandon Galderisi
                      *    Last modified: 07-Oct-2008
                      *    Purpose:       splits an input string (@DelimitedString) 
                      *                   on a delimiter (@delimiter) and outputs 
                      *                   a table of values.
                      *    
                      *
                      *************************************************************/
                      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
                      /*
                      -- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
                      -- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
                      -- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
                      -- numbers table.
                      Requires a nunbers table or this view:
                      create view vw_Nums
                      as
                      with   cte0 as (select 1 as c union all select 1), 
                             cte1 as (select 1 as c from cte0 a, cte0 b), 
                             cte2 as (select 1 as c from cte1 a, cte1 b), 
                             cte3 as (select 1 as c from cte2 a, cte2 b), 
                             cte4 as (select 1 as c from cte3 a, cte3 b), 
                             cte5 as (select 1 as c from cte4 a, cte4 b), 
                             nums as (select row_number() over (order by c) as n from cte5)
                             select n from nums 
                      
                      
                      
                      Sample Usage:
                      select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
                      select theValue from [dbo].[fn_DelimitedToTable]('a','|')
                      select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
                      */

Select all Open in new window

3. Enjoy

The code is ready

You are now ready to use it.  Above in the comment you will see that I have 3 sample calls.

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

The first shows using a multi character delimiter.  This is useful because single character delimiters mean that that character is not valid inside of the string you are parsing.  Depending on your needs, that may be OK.  For example, a comma (,) is often a good delimiter.  The output of the query is 3 columns.  

Ident - An identity field representing the "chunk" of the string.
thePosition - The starting position of the string.
theValue - The value!

The first query returns the following:
1      1      a
2      6      basdf
3      15      c
4      20      d

Second:
a

Third:
1      1      a
2      3      basdf
3      9      c
4      11      d

This shows that many different delimiters, including a space, can be used to output data.


Now back to the item list example.  Let's say that you have a table called items.  This code sample will setup an items table, populate it with data, and show you how to select data from is using the function with a stored procedure.

create table #items
                           (item_id       int not null identity primary key clustered
                           ,item_name     nvarchar(32)
                           )
                      go
                      insert into #items(item_name) values('Hammer')
                      insert into #items(item_name) values('Saw')
                      insert into #items(item_name) values('Drill')
                      GO
                      create procedure up_GetMyItems @ItemList nvarchar(1000), @Delimiter nvarchar(100)
                      as
                      
                      select * from #items i
                       join (select cast(theValue as int) item_id from dbo.fn_DelimitedToTable(@ItemList,@Delimiter)) iFilter
                        on i.item_id = iFilter.item_id
                      
                      go
                      
                      exec up_GetMyItems @itemlist = '1,2', @Delimiter=','
                      exec up_GetMyItems @itemlist = '2,3', @Delimiter=','
                      exec up_GetMyItems @itemlist = '1,3', @Delimiter=','
                      go
                      drop procedure up_GetMyItems 
                      drop table #items

Select all Open in new window

This topic is also covered on my site SQL Server Nation under the title Converting a delimited string into a table.  Stop over there and check out the site!