<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Delimited String Parsing in SQL Server 2005 and later

Published on
20,789 Points
12,789 Views
5 Endorsements
Last Modified:
Approved
Community Pick
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
  • 2
  • 2
5 Comments
LVL 61

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 61

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

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month