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.

Function to Remove Duplicates from Comma Separated String in SQL Server

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

Good luck to everyone in business and life.

56 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!