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