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

Splitting Text - SQL Server

RSS
Modified on Tue, Mar 01, 2011, 8:45 AM by Administrator Categorized as 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

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