Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Split-Into-Table Functions - SQL Server

Split into Table of Integers¶

CREATE FUNCTION dbo.SplitIntoIntegerTable (
     @input     nvarchar(max)
    ,@Delimiter nvarchar(2)
    )
RETURNS @Values TABLE (ID int NOT NULL) AS BEGIN
/*--- Begin Testing ---*/
/*
declare
     @input     nvarchar(max) = '9,2,4,9,7,3,5'
    ,@Delimiter nvarchar(2)   = ','

declare @Values TABLE (ID int NOT NULL)
*/
/*--- End Testing ---*/

/*--- Declarations ---*/
declare 
     @doc       xml
    ,@s         nvarchar(max)

/*--- Inits ---*/
set @s   = replace(@Input, '&', '&')

set @s   = '<x><v>' + replace(@s, @Delimiter, '</v></x><x><v>') + '</v></x>'

set @doc = convert(xml, @s)

/*--- Load @Values table ---*/
insert into 
    @Values (ID)
select
     v  = T.c.value('v[1]', 'int')
from
    @doc.nodes('x') T(c)

/*--- Begin Testing ---*/
/*
select * from @Values
*/
/*--- End Testing & Begin Footer ---*/
RETURN
END
/*--- End Footer ---*/

Split into Table of Strings¶

CREATE FUNCTION dbo.SplitIntoStringTable (
     @input     nvarchar(max)
    ,@Delimiter nvarchar(2)
    )
RETURNS @Values TABLE (ID nvarchar(128) NOT NULL) AS BEGIN
/*--- Begin Testing ---*/
/*
declare
     @input     nvarchar(max) = '9,2,4,9,7,3,5'
    ,@Delimiter nvarchar(2)   = ','

declare @Values TABLE (ID int NOT NULL)
*/
/*--- End Testing ---*/

/*--- Declarations ---*/
declare 
     @doc       xml
    ,@s         nvarchar(max)

/*--- Inits ---*/
set @s   = replace(@Input, '&', '&')

set @s   = '<x><v>' + replace(@s, @Delimiter, '</v></x><x><v>') + '</v></x>'

set @doc = convert(xml, @s)

/*--- Load @Values table ---*/
insert into 
    @Values (ID)
select
     v  = T.c.value('v[1]', 'nvarchar(128)')
from
    @doc.nodes('x') T(c)

/*--- Begin Testing ---*/
/*
select * from @Values
*/
/*--- End Testing & Begin Footer ---*/
RETURN
END
/*--- End Footer ---*/