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