Delimited String Parsing in SQL Server 2005 and later: parse, string parsing, delimited string, user defined function, sql server 2005, sql server 2008

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

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',' ')
*/

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

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!

Delimited String Parsing in SQL Server 2005 and later

Create the view the function depends on

Create the function

Enjoy

parse

string parsing

delimited string

user defined function

sql server 2005

sql server 2008

SQL Server 2005

About The Author

More Articles From BrandonGalderisi

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server 2005 Experts