Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Split-Into-Table Functions - SQL Server

RSS
Modified on Thu, Jun 26, 2014, 4:26 PM by Administrator Categorized as 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, '&', '&amp;')

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 ---*/

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.