<

Go Premium for a chance to win a PS4. Enter to Win

x

Delimited String Parsing in SQL Server 2005 and later

Published on
20,346 Points
12,346 Views
5 Endorsements
Last Modified:
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 

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

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

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!
5
Comment
  • 2
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
Nice, Brandon.  The common table expression to generate the numbers view always impresses me.
0
 
LVL 39

Author Comment

by:BrandonGalderisi
I'll mention again that I'm not the author of that CTE!
0
 
LVL 60

Expert Comment

by:Kevin Cross
Who wrote that then, Tim?
0
 
LVL 39

Author Comment

by:BrandonGalderisi
This was posted over at SQL Server Central where I posted use of the same CTE (referencing there that I was not the original author of the CTE as well).


Just an FYI... the source of the CTE in your article is by a fellow called Itzik Ben-Gan and can be found at the bottom of page 255 in a book titled "Inside Microsoft SQL Server 2005 - T-SQL Querying".

--Jeff Moden
0
 
LVL 18

Expert Comment

by:Rajar Ahmed
nice presentation . i enjoyed reading it .
Short and sweet

Meeran03
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month