2005 - Splitting a CSV - 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 2005 Forums

Transact-SQL (2005)

2005 - Splitting a CSV

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2007-05-22 : 09:10:57

Hello everyone.

I'm dabbling in 2005 SQL after a long time doing client side.

Did 2005 introduce any newer simpler techniques to split a CSV delimited parameter string?

Sam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-22 : 09:29:48

no not really anything simpler...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 09:34:35

obsessed with CSV ?

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-22 : 10:49:35

quote:
Originally posted by SamC

Hello everyone.

I'm dabbling in 2005 SQL after a long time doing client side.

Did 2005 introduce any newer simpler techniques to split a CSV delimited parameter string?

Sam

If you want to go "New School", then instead of a comma-delimited string, pass in the value as xml. This would be the "preferred" way of doing things.

SamC
White Water Yakist

3467 Posts

Posted - 2007-05-22 : 10:55:10

quote:
Originally posted by rudesyle

If you want to go "New School", then instead of a comma-delimited string, pass in the value as xml. This would be the "preferred" way of doing things.

I'm a simpleton. I have a CSV splitting UDF from the SQL 2000 days. If I were building a significant application, I'd be keen on taking advantage of 2005's XML features. This is a one-shot survey question on a web-page.

Sam

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 11:53:32

I discovered recently that my CSV-splitter-function had terrible performance, and I revisited the issue and now think I have something much faster - but it may only be as good as you were already using!

I topped-up your original thread with the details, informal timings here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2#305425

(The code is higher up that thread)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-22 : 12:28:06

well... if you really want something fancy... just for you in SS2k5:


DECLARE @text NVARCHAR(max)
SELECT @text = 'gfas,gds,gdasg,dag,dahdafhda,hahdfhad,hdfahdahaae,dhyahydhydhrdeys,yhgdrgy'

DECLARE @textXML XML
SELECT @textXML = CAST('<d>' + REPLACE(@text, ',', '</d><d>') + '</d>' AS XML)
SELECT @textXML 
SELECT  T.split.value('.', 'nvarchar(max)')
FROM	@textXML.nodes('/d') T(split)

EDIT: A little shorter code
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources