July 29, 2002 at 10:42 am
Hi
I want to parse a delimited varchar column. Can it be done using a single statement?
I dont want to use a cursor. UDFs are allowed.
create table a (i int, s varchar(64))
insert into a select 1, 'a, b, c'
insert into a select 2, 'e, g'
-- SQL code
Result:
i s
- -
1 a
1 b
1 c
2 e
2 g
/ Fredrik
July 29, 2002 at 12:02 pm
You could use a while loop to build the results, but it might be ugly if this is large.
I'm sure there is something, but can't think off the top of my head.
Steve Jones
July 29, 2002 at 12:27 pm
You would replace the delimiters with end/start tags("</ROW><ROW>"), add a start tag at the beginning of the string, and end tag at the very end, wrap the whole thing in another xml tag set (<DOC></DOC>), open as a table using OpenXML and do the insert.
Andy
July 29, 2002 at 12:56 pm
Now that be slick!
Steve Jones
July 31, 2002 at 12:56 am
Nice. But
Server: Msg 6604, Level 16, State 1, Procedure sp_xml_preparedocument, Line 4
XML stored procedures are not supported in fibers mode.
July 31, 2002 at 4:33 am
Now there is something else I didnt know.
Andy
October 12, 2002 at 10:18 am
Ditto
There are a number of scripts on this website for udfs that return a table of values from a delimited list.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply