I have got it...

create function Split_fnOK

(

@datavarchar(8000),

@deli_char varchar(3)

)

returns @list table

(

Idxint,

datavarchar(8000)

)

as

begin

declare @from_locint

declare @to_locint

if charindex(@deli_char,@data,0) <= 0

begin

insert into @list(Idx, data) values (1, @data)

return

end

if charindex(@deli_char,@data,0) > 0

begin

select @from_loc= 0

select @to_loc= charindex(@deli_char,@data,0)

end

if charindex(@deli_char,@data,0) <= 0

begin

select @to_loc = null

end

while @to_loc is not null

begin

if substring(@data,@from_loc, @to_loc - @from_loc) <> ''

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)) <> ''

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

return

end

go

With that set up, I have got much better stats time/cpu and I/O. Good.

Cheers