Hi,

I've been using a UDF split function courtesy of 4GuysFromRolla.com for a few years (2004) but I'm currently working on a project where I need to pass in quite a long list (65+) and I'm finding that it's causing a bottle neck and I wondered if anyone had any ideas on how to make it more efficient?

Code:
IF Exists (SELECT * FROM sysobjects WHERE ID=OBJECT_ID('Split') AND type IN('FN','TF')) BEGIN
	DROP FUNCTION [dbo].[Split]
END
Go
CREATE FUNCTION dbo.Split
	(
		@List nvarchar(max),
		@SplitOn nvarchar(5)
	)  
	RETURNS @RtnValue table 
	(
		Id int identity(1,1),
		Value nvarchar(100)
	) 
	AS  
	BEGIN 
		While (Charindex(@SplitOn,@List)>0) Begin
			Insert Into @RtnValue (value)
			Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
		End

		Insert Into @RtnValue (Value)
		Select Value = ltrim(rtrim(@List))
	
		Return
	END
Regards Al