SQLServerCentral.com / Development / SQL Server 2005 / Split strings alternative to XML / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:37:47 GMT20RE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspx[quote][b]MackF (3/29/2013)[/b][hr]I am now testing this one:CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))RETURNS TABLEASRETURN 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&gt;0 ) SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value FROM aExecution 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 :)[/quote]From your original post you stated that you wanted to find an alternative to an XML splitter.[quote]For some performance considerations, we would like to try alternative to the code below.[/quote]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.Fri, 29 Mar 2013 07:38:38 GMTSean LangeRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxI am now testing this one:CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))RETURNS TABLEASRETURN 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&gt;0 ) SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value FROM aExecution 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 :)Fri, 29 Mar 2013 04:57:41 GMTMackFRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspx[quote][b]MackF (3/27/2013)[/b][hr]I have got it...create function Split_fnOK( @data varchar(8000), @deli_char varchar(3) )returns @list table( Idx int, data varchar(8000))asbegin declare @from_loc int declare @to_loc int if charindex(@deli_char,@data,0) &lt;= 0 begin insert into @list(Idx, data) values (1, @data) return end if charindex(@deli_char,@data,0) &gt; 0 begin select @from_loc = 0 select @to_loc = charindex(@deli_char,@data,0) end if charindex(@deli_char,@data,0) &lt;= 0 begin select @to_loc = null end while @to_loc is not null begin if substring(@data,@from_loc, @to_loc - @from_loc) &lt;&gt; '' begin insert into @list(Idx, data) select isnull(max(Idx),0) + 1, substring(@data,@from_loc, @to_loc - @from_loc) from @list end select @from_loc = charindex(@deli_char,@data,@from_loc+len(@deli_char)) + len(@deli_char) select @to_loc = charindex(@deli_char,@data,@from_loc) if @to_loc = 0 begin if substring(@data,@from_loc, (len(@data) - @from_loc) + len(@deli_char)) &lt;&gt; '' begin insert into @list(Idx, data) select isnull(max(Idx),0) + 1, substring(@data,@from_loc, (len(@data) - @from_loc) + len(@deli_char)) from @list end select @to_loc = null end end returnend goWith that set up, I have got much better stats time/cpu and I/O. Good.Cheers[/quote]Show us 2 lines of sample data to split so we can show you how to blow the doors off of everything but a CLR and come pretty close to that, to boot.Thu, 28 Mar 2013 20:05:58 GMTJeff ModenRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspx[quote][b]MackF (3/27/2013)[/b][hr]I have got it......With that set up, I have got much better stats time/cpu and I/O. Good.Cheers[/quote]This is going to be a LOT slower than the methods Lynn suggested. Actually orders of magnitude slower. I know you have been pointed to it before but check out the article in my signature about splitting strings. Then compare your looping function to DelimitedSplit8K and see which one is faster. :-PThu, 28 Mar 2013 08:03:43 GMTSean LangeRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxI have got it...create function Split_fnOK( @data varchar(8000), @deli_char varchar(3) )returns @list table( Idx int, data varchar(8000))asbegin declare @from_loc int declare @to_loc int if charindex(@deli_char,@data,0) &lt;= 0 begin insert into @list(Idx, data) values (1, @data) return end if charindex(@deli_char,@data,0) &gt; 0 begin select @from_loc = 0 select @to_loc = charindex(@deli_char,@data,0) end if charindex(@deli_char,@data,0) &lt;= 0 begin select @to_loc = null end while @to_loc is not null begin if substring(@data,@from_loc, @to_loc - @from_loc) &lt;&gt; '' begin insert into @list(Idx, data) select isnull(max(Idx),0) + 1, substring(@data,@from_loc, @to_loc - @from_loc) from @list end select @from_loc = charindex(@deli_char,@data,@from_loc+len(@deli_char)) + len(@deli_char) select @to_loc = charindex(@deli_char,@data,@from_loc) if @to_loc = 0 begin if substring(@data,@from_loc, (len(@data) - @from_loc) + len(@deli_char)) &lt;&gt; '' begin insert into @list(Idx, data) select isnull(max(Idx),0) + 1, substring(@data,@from_loc, (len(@data) - @from_loc) + len(@deli_char)) from @list end select @to_loc = null end end returnend goWith that set up, I have got much better stats time/cpu and I/O. Good.CheersWed, 27 Mar 2013 17:57:11 GMTMackFRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxI am trying...but I do not succeed in adapting the 8k function.Don't know how to "integrate" the &lt;d&gt;, &lt;d/&gt;, etc stuff as they exist in the initial function...I really suckWed, 27 Mar 2013 16:24:32 GMTMackFRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxThe two T-SQL functions, DelimitedSplit8K and DelimitedSplitN4K, that are attached to the article I referred you to are optimized to split varchar(8000) and nvarchar(4000) strings.I would read the article and discussion again, as use these functions on appropriate sized strings to see how they work.For string longer than varchar(8000) and nvarchar(4000) you will probably want to go to a CLR splitter as it will out perform a T-SQL based splitter.Wed, 27 Mar 2013 15:28:24 GMTLynn PettisRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxNot sure this is absolutely necessary, maybe varchar(some values) could fit, but as of now, I do not know "where" they could use this function to split "what" amout of data...It could some comments in a text box as it is commercial app...nvarchar(max) type should be tested at least to compare performance.Wed, 27 Mar 2013 15:17:08 GMTMackFRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxDoes your function have to split a string declared as nvarchar(max)?Wed, 27 Mar 2013 15:02:11 GMTLynn PettisRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxThanks Lynn,...I have gone through the article, too much complicated to me and definitely not my stuff...The thing is I know the function above is killing perf, but I do not know how to write something else despite my readings.The goal is to submit some results to the dev team and show how crappy then can produce their coding when not being concerned by global performance...If anyone can "translate" this little function, ideally not in CLR, that would be helpful :)Wed, 27 Mar 2013 15:00:22 GMTMackFRE: Split strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxCheck out the resources with this article: [b][url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url][/b]. You should find a CLR splitter that will meet your needs.Be sure to read the article and the discussion with it.Wed, 27 Mar 2013 14:44:41 GMTLynn PettisSplit strings alternative to XMLhttp://www.sqlservercentral.com/Forums/Topic1436162-145-1.aspxHi guys,For some performance considerations, we would like to try alternative to the code below.Anyone could help on how to get this job done using CTE, temp table or I don not know...Actually, I am not a dev guy at all ::-)Thanks,MackCREATE FUNCTION [dbo].[fn_Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))RETURNS @t TABLE (Idx int identity (1,1), data NVARCHAR(max))ASBEGIN DECLARE @textXML XML; SELECT @textXML = CAST('&lt;d&gt;' + REPLACE(@data, @delimiter, '&lt;/d&gt;&lt;d&gt;') + '&lt;/d&gt;' AS XML); INSERT INTO @t(data) SELECT T.split.value('.', 'nvarchar(max)') AS data FROM @textXML.nodes('/d') T(split) RETURNWed, 27 Mar 2013 14:36:58 GMTMackF