Hi all,

Its a new way to split the string using XML. I have written a SQL function to split the string without using XML. Please refer the below code...

CREATE function Split_fn

(

@split_stringvarchar(max),

@deli_charvarchar(3)

)

returns @list table

(

SeqNoint,

SplitStringvarchar(max)

)

as

begin

declare @from_locint

declare @to_locint

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

go

select * from dbo.split_fn('raja,ravi,prabhu',',')

Rafidheen.M