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
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',' ')
*/
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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (5)
Commented:
Author
Commented:Commented:
Author
Commented: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
Commented:
Meeran03