Page History: Splitting Text - SQL Server

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Tue, Jul 27, 2010, 2:23 PM

Given the text "See:Dick:and:Jane:run", the following SQL returns a rowset of data with five rows - one row for each word in the input text.

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)