function to remove duplicates from comma delimited list sql server
-- ===========================================================================
-- 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