SQLServerCentral / Discuss Content Posted by Jeff Moden / Article Discussions / Article Discussions by Author / Testing other functions of splitting strings / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 13:48:05 GMT20RE: Testing other functions of splitting stringshttp://www.sqlservercentral.com/Forums/Topic1400814-203-1.aspx[quote][b]Dirk vd Watt (12/28/2012)[/b][hr]Hi,Can you please test your new functions' performance against the following function :CREATE FUNCTION [dbo].[ParseString] ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( WITH Tokens(LinePos, StartPos, EndPos) AS ( SELECT 1, 1, CharIndex(@Delimiter, @String) UNION ALL SELECT LinePos + 1, EndPos + 1, CharIndex(@Delimiter, @String, EndPos + 1) FROM Tokens WHERE EndPos > 0 ) SELECT CONVERT(Int, LinePos) AS RowNumber, SubString ( @String, StartPos, CASE WHEN EndPos > 0 THEN (EndPos - StartPos) ELSE 8000 END ) AS StringValue from Tokens )and let us know which performs best ?Regards,Dirk van der Watt[/quote]Don't really need to, this is a recursive cte type splitter. Pretty confident that the DelimitSplit8K will out perform it.Fri, 28 Dec 2012 08:04:15 GMTLynn PettisRE: Testing other functions of splitting stringshttp://www.sqlservercentral.com/Forums/Topic1400814-203-1.aspxFor anybody just stumbling in here, this was a response posted in another thread. [url=http://www.sqlservercentral.com/articles/Tally+Table/72993/]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]For the sake of anybody else coming here please see the link I referred to above. It goes into great detail how to split strings and tests the performance of a number of different approaches.Fri, 28 Dec 2012 07:55:07 GMTSean LangeTesting other functions of splitting stringshttp://www.sqlservercentral.com/Forums/Topic1400814-203-1.aspxHi,Can you please test your new functions' performance against the following function :CREATE FUNCTION [dbo].[ParseString] ( @String VarChar(8000), @Delimiter VarChar(1) ) RETURNS TABLEASRETURN ( WITH Tokens(LinePos, StartPos, EndPos) AS ( SELECT 1, 1, CharIndex(@Delimiter, @String) UNION ALL SELECT LinePos + 1, EndPos + 1, CharIndex(@Delimiter, @String, EndPos + 1) FROM Tokens WHERE EndPos > 0 ) SELECT CONVERT(Int, LinePos) AS RowNumber, SubString ( @String, StartPos, CASE WHEN EndPos > 0 THEN (EndPos - StartPos) ELSE 8000 END ) AS StringValue from Tokens )and let us know which performs best ?Regards,Dirk van der WattFri, 28 Dec 2012 01:04:08 GMTDirk vd Watt