Background
Unfortunately splitting a String into rows was lacking in Transact SQL for a while.
MSFT changed that in Version 2016.
Split_String is capable and handy.
Here is the link.
Prior to v2016
Prior to Version 2016, there are many tools out in the wild for accomplishing same.
There are CLR Functions. There are functions that use dbo.Numbers
XML
And, there are functions that rely on XML’s inherent ability to split an input into nodes.
XML
Standing On
Found a nice working model on sqlhints.com.
BTW, sqlHints.com is Basavaraj Biradar.
And, here is the particular post that we will be using.
It is titled “How to Split comma or any other character delimited string into a Table in Sql Server.”
Objective
- Look for separator
- Replace separator with endNode and beginNode
- Close out endNode
- Start new node with beginNode
- Replace separator with endNode and beginNode
- Begin & End fragment
- Begin Fragment with beginNode
- End Fragment with endNode
- Start and end node fragment
- XML needs root node
- And, so we start string with rootNode
- And, end with rootNode
- XML needs root node
- Use XQuery to parse node
- Parse node using XQuery
- Nodes ( “//node”)
Code
[stringSplit].[itvf_stringSplitUsingXML]
use [master] go if schema_id('stringSplit') is null begin exec('create schema [stringSplit] authorization [dbo] ') end go if object_id('[stringSplit].[itvf_stringSplitUsingXML]') is null begin exec ( 'create function [stringSplit].[itvf_stringSplitUsingXML] () RETURNS @RESULT TABLE ( Value VARCHAR(MAX) ) as begin return end ' ) end go ALTER FUNCTION [stringSplit].[itvf_stringSplitUsingXML] ( @string VARCHAR(MAX) , @separator CHAR(1) = ',' ) RETURNS @tblResult TABLE ( [rowNumber] smallint not null identity(1,1) , [value] VARCHAR(600) , [xml] xml , [element] xml ) AS BEGIN /* a) sqlhints.com http://sqlhints.com/tag/split-comma-separated-values-in-sql/ */ DECLARE @xml XML DECLARE @xmlAsString nvarchar(max) declare @rootBegin varchar(10) declare @rootEnd varchar(10) declare @node varchar(10) declare @nodeBegin varchar(10) declare @nodeEnd varchar(10) declare @xmlNodeAsString as varchar(600) set @rootBegin = '<root>' set @rootEnd = '</root>' set @node = 'node' set @nodeBegin = '<node>' set @nodeEnd = '</node>' /* Look for separator, when found end earlier node by replacing separator with [nodeEnd] and starting new node with [nodeBegin] */ set @xmlNodeAsString = REPLACE ( @string , @separator , @nodeEnd + @nodeBegin ) /* Bracket fragment with begin and end node */ SET @xmlNodeAsString = @nodeBegin + @xmlNodeAsString + @nodeEnd /* Root node separated out contents End Root Node */ SET @xmlAsString = @rootBegin + @xmlNodeAsString + @rootEnd /* Convert to XML */ set @xml = @xmlAsString INSERT INTO @tblRESULT ( [value] , [xml] , [element] ) SELECT [value] = ( t.i.value ( '(.)[1]' , 'varchar(max)' ) ) , [xml] = @xml , [element] = ( t.i.query ('.') ) from @xml.nodes('//node') AS t(i) RETURN END go
Invoke
declare @data varchar(600) set @data = 'sammie,bobbie,jackie' select * from [master].[stringSplit].[itvf_stringSplitUsingXML] ( @data , default )