Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Split strings alternative to XML Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 8:39 AM
Points: 8, Visits: 69
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 :)



Post #1436867
Posted Friday, March 29, 2013 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 13,253, Visits: 12,087
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 :)


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)
Post #1436923
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse