szlamany,

Thanks very much for your pointers I've changed the function as follows although I'm not sure my changes to step along the @List variable are very efficient I'd appreciate it if you could have a look and let me know if there is a better way of doing this -

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 varchar(max),
		@SplitOn varchar(5)
	)  
	RETURNS @RtnValue table 
	(
		Id int identity(1,1),
		Value varchar(100)
	) 
	AS  
	BEGIN
		DECLARE @IdxS INT=0 -- remember the last index so we know where the start position is for each val in the list 
		DECLARE @Idx INT=CHARINDEX(@SplitOn,@List)
		DECLARE @Val VARCHAR(100)

		While (@Idx>0) BEGIN
			SET @Val=LTrim(RTrim(SubString(@List,@IdxS,@Idx-@IdxS)))
			
			Insert Into @RtnValue (value)
			Select Value = @Val			
			SET @IdxS=@Idx+1
			SET @Idx=CHARINDEX(@SplitOn,@List,@Idx+1)
			IF @Idx=0 BEGIN SET @Val=LTrim(RTrim(SubString(@List,@IdxS,(LEN(@List)+1)-@IdxS))) END
		End
		Insert Into @RtnValue (Value)
		Select Value = LTrim(RTrim(COALESCE(@Val,@List)))
		
	
		Return
	END
Cheers Al