Splitting Text - SQL Server

Given the text "See:Dick:and:Jane:run", we would like a SQL statement to return a rowset of data with five rows - one row for each word in the input text.

A First Try¶

{copytext|div1}

declare
     @Input varchar(500)
    ,@doc   xml

select
     @Input = 'See:Dick:and:Jane:run'
    ,@doc   = convert(xml, '<x v="' + replace(@Input, ':', '"/><x v="') + '"/>')

select
     v = T.c.value('.', 'varchar(100)')
from
    @doc.nodes('x/@v') T(c)

A More Robust Version¶

The above version doesn't handle ampersands or double-quotes; The following version does.

{copytext|div2}

declare
     @Input varchar(500)
    ,@s     varchar(500)
    ,@doc   xml

select
     @Input = 'See Dick say to the A&P clerk, "Hello, there."'
    ,@s     = replace(@Input, '&', '&amp;')
    ,@doc   = convert(xml, '<x><v>' + replace(@s, ' ', '</v></x><x><v>') + '</v></x>')

select
     v = T.c.value('.', 'varchar(100)')
from
    @doc.nodes('x/v') T(c)

Hierarchy IDs¶

The following function will parse out the elements of a hierarchy path specified by a hierarchyID

{copytext|Vectorize}

create function dbo.Vectorize(@i hierarchyID)
returns @t table
    (
     position int identity(1,1) not null
    ,nodeValue int not null
    ) as begin

--declare @i hierarchyID = '/1/6/3/4/'

declare
     @Input varchar(500) = @i.ToString()
    ,@s     varchar(max)
    ,@doc   xml

select
     @Input = SUBSTRING(@input,2, len(@Input)-2)     
    ,@s     = replace(@Input, '&', '&amp;')
    ,@doc   = convert(xml, '<x><v>' + replace(@s, '/', '</v></x><x><v>') + '</v></x>')

insert into @t (
    nodeValue
    )
select
     v = T.c.value('.', 'int')
from
    @doc.nodes('x/v') T(c)
    
return
end