Split Function Script in T-SQL

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE FUNCTION [dbo].[SplitString]

(

@str varchar(100),

@delchar varchar(1)

)

RETURNS @mytbl table(val varchar(100)) 

AS

begin

declare @tstr varchar(100)

declare @i int

declare @j int

declare @strlen int

declare @charat varchar(1)

set @strlen = len(@str)

set @i = 1

set @tstr = ''

while(@i <= @strlen)

begin

set @charat = substring(@str,@i,1)

if(@charat <> @delchar)

set @tstr = @tstr + @charat

else

begin

insert into @mytbl values(@tstr)

set @tstr = ''

end

set @i = @i + 1

end

insert into @mytbl values(@tstr)

return

end

----------------------------------------------------------------------------------------------

To use above function write:

select * from SplitString('anil,tejas,nilesh,paresh,yashesh,manish',',')

Output will be...

anil

tejas

nilesh

paresh

yashesh

manish