Function to Remove Duplicates from Comma Separated String in SQL Server

Hello to everyone,

In this article, I will try to give information about the function that removes duplicates from a comma-separated string in SQL Server.

In SQL Server you may want to remove duplicates from comma separated string in some cases

You can easily do this using the function below.

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


--Use of Function 

SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') 

When you create and run the above Function, you will see the following result.

As you can see, the repeated list has been reduced to one.

Good luck to everyone in business and life.