SQL Server User Defined Function to remove duplicate entries from a comma delimited string (CSV). It takes comma delimited string and then converts it to table and runs DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used.

-- ===========================================================================
-- Remove Duplicate Entry from Comma Delimited String
--
-- Given the string "test,test2,test,test3,test2" returns "test,test2,test3".
--
-- SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
--
-- http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/
-- http://stackoverflow.com/a/20883509
--
-- Usage:
-- select dbo.DistinctList(ColumnName) as DupeFreeString from YourTable;
-- ===========================================================================

-- Drop if exists:
if exists(select * from sys.objects
    where object_id = OBJECT_ID(N'[dbo].[DistinctList]')
    and type in(N'FN', N'IF', N'TF', N'FS', N'FT'))
begin
    drop function dbo.DistinctList
end;
go

create function dbo.DistinctList(
    @List varchar(max),
    @Delim char)
returns varchar(max)
as
    begin
        declare @ParsedList table(
            Item varchar(max)
        );

        declare @list1 varchar(max);
        declare @Pos int;
        declare @rList varchar(max);

        set @list = LTRIM(RTRIM(@list)) + @Delim;
        set @pos = CHARINDEX(@delim, @list, 1);

        while @pos > 0
        begin
            set @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)));
            if @list1 <> ''
                begin
                    insert into @ParsedList values (CAST(@list1 as varchar(max)))
                end;
            set @list = SUBSTRING(@list, @pos + 1, LEN(@list));
            set @pos = CHARINDEX(@delim, @list, 1);
        end;

        select @rlist = COALESCE(@rlist + ',', '') + item
        from (select distinct Item from @ParsedList) as t;

        return @rlist;
    end;
go