parse string to rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

parse string to rows

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-09-01 : 10:53:09

I have a table with a field of varchar(max) with has a delimited string:
'1 jan 2011, 10 jan 2011, 16 jan 2011' ...etc along with an ID field for each row.

I need to split the string into individual rows - I'm guessing I need to split to columns first then into rows. But the final result needs to be:
ID DateField
1 1 jan 2011
1 10 jan 2011
1 16 jan 2011
2 2 jan 2011
2 8 jan 2011
2 16 jan 2011 ..etc

Any examples appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-01 : 11:31:44

You need a string Parse/Split function. There are lots of them out there. Here is a link to a bunch of them:
http://www.sommarskog.se/arrays-in-sql-2005.html

Since you are using VARCAHR(MAX) you are bit limited in that the most performant split functions are geared towards the non-MAX strings (i.e. VARCAHR(8000)).

lappin
Posting Yak Master

182 Posts

Posted - 2011-09-01 : 11:50:48

Hi - I've tried some of these but they seem to use a single string passed in, I want to apply a function to each row. I can use varchar(8000).
Here is my test table:

CREATE TABLE [dbo].[testTable](
[ID] [nchar](10) NULL,
[dateField] [varchar](8000) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[testTable]
([ID]
,[dateField])
VALUES
(1
,'1 jan 2011, 10 jan 2011, 16 jan 2011')
GO
INSERT INTO [dbo].[testTable]
([ID]
,[dateField])
VALUES
(2
,'8 jan 2011, 16 jan 2011, 26 jan 2011')

I have a split function which returns a string as rows but not sure how to join it to my table. I've also modified a split function to take the ID field as a parameter but I'm not sure if I'm going down a dead end.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-01 : 11:56:57

Is this a one time thing or something that is done once and a while? If so, you could use a loop or a cursor to load up a table or a temp table and then use that after you are done parsing. If you need to do this regularly, then you should look at fixing your schema.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-01 : 12:00:47

You can also use the CROSS APPLY operator:

SELECT
*, D.Value
FROM testTable AS T
CROSS APPLY
(
    SELECT Value
    FROM [dbo].[SplitFunctionName](T.dateField, ',') AS DD
) AS D

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-01 : 13:36:27

There's also this technique: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

lappin
Posting Yak Master

182 Posts

Posted - 2011-09-02 : 05:15:21

Thanks for all your help. The CROSS APPLY worked great. It is for occassional transformation from another system - so can't change schema. - thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources