Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split strings alternative to XML


Split strings alternative to XML

Author
Message
MackF
MackF
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 78
I am now testing this one:

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
WITH a AS(
SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
UNION ALL
SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
FROM a
WHERE idx2>0
)
SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
FROM a

Execution is more simple, and performs same than above with less CPU time consumed for optimizations then...


Ma data to parse could be just like that 'toto.toto,toto.toto,toto.'
Simple Smile



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
MackF (3/29/2013)
I am now testing this one:

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
WITH a AS(
SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
UNION ALL
SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
FROM a
WHERE idx2>0
)
SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
FROM a

Execution is more simple, and performs same than above with less CPU time consumed for optimizations then...


Ma data to parse could be just like that 'toto.toto,toto.toto,toto.'
Simple Smile


From your original post you stated that you wanted to find an alternative to an XML splitter.


For some performance considerations, we would like to try alternative to the code below.


I don't quite understand why you keep looking at slower alternatives than the one suggested. This is like going to a car dealer and telling them you want to have the fastest car on the lot. The guy show you the Lamborghini, you smile and nod your head and walk over the Pinto. You test drive it and it is in fact faster than the Pacer you are driving currently. The salesman reminds you that for the same price ($0 in t-sql land) that you could drive the MUCH MUCH MUCH faster Aventador but you say you want to keep testing out the Pinto. It just doesn't make sense. Read the article about the tally table splitter, look at the performance tests. There is some code that is almost identical to the one you posted. It was tested in that article and it was found to be magnitudes slower. Stop driving the Pinto and accept the free keys to your new high performance sports car.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search