Yeah I used such function a lot in our applications although it is nice to have the ability to call with different delimiters (sometimes people don't like to use commas :-)...)

But recently I have been looking at it from a different point of view by using CLR functions and the speed of it is mind blowing compare to the T-SQL code.

Only limitation though is that the CLR function doesn't accept a string as long as the T-SQL one (or maybe I haven't found the correct configuration yet.

CLR might be very useful in this case so I wanted to give you the code if you want to try it.

Cheers

public partial class UserDefinedFunctions

{

[SqlFunction(Name = "ufn_SplitString", FillRowMethodName = "SplitString_FillRow", TableDefinition = "ID NVARCHAR(255)")]

public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)

{

if (delimiter.Length == 0)

return new string[1] { str.Value };

return str.Value.Split(delimiter[0]);

}

public static void SplitString_FillRow(object row, out SqlString str)

{

str = new SqlString((string)row);

}

};