Search This Blog & Web

Monday, August 3, 2009

Returning table from ; seperated values

create function [dbo].[fn_split](
@str varchar(8000),
@delimiter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @pos int
select @str = @str + @delimiter
While len(@str) > 0
begin
select @pos = charindex(@delimiter,@str)
if @pos = 1
insert @returnTable (item)
values (null)
else
insert @returnTable (item)
values (substring(@str, 1, @pos-1))

select @str = substring(@str, @pos+1, len(@str)-@pos)
end
return
end

1 comment:

Saqi said...

Fantastic function, it resolve by comma separated issue.