Remove Duplicate Entry from Comma Delimited String

//////////// Sql Function

Create FUNCTION RemoveDuplicateFrmStr

(

@Str VARCHAR(MAX),

@SplitBy CHAR

)

RETURNS

VARCHAR(MAX)

AS

BEGIN

DECLARE @tblStr TABLE

(

Item VARCHAR(MAX)

)

DECLARE @StrS VARCHAR(MAX), @Pos INT, @StrF VARCHAR(MAX)

SET @Str = LTRIM(RTRIM(@Str)) + @SplitBy

SET @pos = CHARINDEX(@SplitBy, @Str, 1)

WHILE @pos > 0

BEGIN

SET @StrS = LTRIM(RTRIM(LEFT(@Str, @pos - 1)))

IF @StrS <> ''

INSERT INTO @tblStr VALUES (CAST(@StrS AS VARCHAR(MAX)))

SET @Str = SUBSTRING(@Str, @pos+1, LEN(@Str))

SET @pos = CHARINDEX(@SplitBy, @Str, 1)

END

SELECT @StrF = COALESCE(@StrF+@SplitBy+' ','')+ item FROM (SELECT DISTINCT Item FROM tblStr)t

RETURN @StrF

END

---------------call-

declare @str varchar(max)='this is is the the most important thing thing'

declare @str1 varchar(max)='this, is, is, the, the, most, important, thing, thing'

select (dbo.RemoveDuplicateFrmStr(@str,' '))em

select (dbo.RemoveDuplicateFrmStr(@str1,','))em

--------------- Result

1. important  is  most  the  thing  this

2. important, is, most, the, thing, this