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

Page History: Splitting Text - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Current Revision


Page Revision: Tue, Aug 10, 2010, 4:22 PM


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)

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