Hi all,

sorry for the late reply...

I agreed with performance issue of SQL function incase of too large string needs to be split.

hence, i have slightly modified the SQL function which makes better performance.

Please try the below code...

CREATE function Split_fn

(

@split_string varchar(8000),

@deli_char varchar(3)

)

returns @list table

(

SeqNo int,

SplitString varchar(8000)

Primary Key(SeqNo)

)

as

begin

declare @from_loc int

declare @to_loc int

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

begin

insert into @list(seqno, SplitString) values (1, @split_string)

return

end

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

begin

select @from_loc = 0

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

end

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

begin

select @to_loc = null

end

while @to_loc is not null

begin

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

begin

insert into @list(seqno, SplitString)

select isnull(max(seqno),0) + 1, substring(@split_string,@from_loc, @to_loc - @from_loc)

from @list

end

select @from_loc = charindex(@deli_char,@split_string,@from_loc+len(@deli_char)) + len(@deli_char)

select @to_loc = charindex(@deli_char,@split_string,@from_loc)

if @to_loc = 0

begin

if substring(@split_string,@from_loc, (len(@split_string) - @from_loc) + len(@deli_char)) <> ''

begin

insert into @list(seqno, SplitString)

select isnull(max(seqno),0) + 1, substring(@split_string,@from_loc, (len(@split_string) - @from_loc) + len(@deli_char))

from @list

end

select @to_loc = null

end

end

return

end

With regards,

Rafidheen.M