SQL SERVER – Remove Duplicate Entry from Comma Delimited String – UDF

I love reader’s contribution this blog as that brings variety in articles. I encourage my readers to provide their contribution and I will publish then with their name. Today’s contribution cleans duplicate values out of a comma delimited string.

Blog Reader Ashish Jain has posted very simple script which will remove duplicate entry from comma delimited string. User Defined Function has very simple logic behind it. 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.

I have modified original contribution from Ashish so now it completely covers the subject intended to cover. I would suggest that this UDF should be kept handy to perform this tedious task easily.

CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @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 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
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) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
GO

I encourage my readers to send their contribution as well so I can include their contribution as well.

Things to Know Before You Clean a Comma Delimited String

The function works well, but the output does not keep the original order. DISTINCT gives no promise about order, so 'b,a,b' may come back as 'a,b'. If order matters to your application, this needs a different approach.

Two more details are easy to miss. The duplicate check follows the collation of your database, so with a case insensitive collation 'AA' and 'aa' count as the same value. Also, the function accepts any delimiter as input, but it always joins the result back with a comma. Keep that in mind if your lists use semicolons or pipes.

On SQL Server 2017 and later, you can do the same job without a loop by using STRING_SPLIT and STRING_AGG:

SELECT STRING_AGG(value, ',') AS DistinctList FROM (SELECT DISTINCT TRIM(value) AS value FROM STRING_SPLIT(@List, ',') WHERE TRIM(value) != '') AS t;

Note that STRING_SPLIT needs database compatibility level 130 or higher. Finally, if you clean the same lists again and again, it may be a sign that the data belongs in a separate table with one value per row. That design makes duplicates easy to prevent with a unique constraint.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.