Declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A<2,B>4,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

CREATE function Split_fn
(
	@split_string	varchar(max), 
	@deli_char		varchar(3)	
)
returns @list table
(
	SeqNo		int,
	SplitString	varchar(max)
)
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 


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