split

CREATE FUNCTION [dbo].[split]

(
@splitstring varchar(max),
@separator CHAR(1) = ','
)
RETURNS @splitstringstable TABLE
(
[item] VARCHAR(200)
)
AS
BEGIN
DECLARE @currentindex INT
DECLARE @nextindex INT
DECLARE @returntext VARCHAR(200)

SELECT @currentindex=1

WHILE(@currentindex<=datalength(@splitstring))
BEGIN
SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
IF(@nextindex=0 OR @nextindex IS NULL)
SELECT @nextindex=datalength(@splitstring)+1

SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)

INSERT INTO @splitstringstable([item])
VALUES(@returntext)

SELECT @currentindex=@nextindex+1
END
RETURN
END