At times, we come across situations where we need to remove duplicate entries from a comma delimited string in SQL Server. This can be a tedious task if done manually, but fortunately, there is a simple solution using a User Defined Function (UDF).

One of our blog readers, Ashish Jain, has contributed a script that effectively removes duplicate entries from a comma delimited string. The UDF takes the input string and converts it into a table. It then performs a DISTINCT operation on the table to remove any duplicate values. Finally, it converts the table back into a string, which can be used as desired.

Here is the modified version of the UDF:

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', ',') AS DistinctList
GO

This UDF can be a handy tool when you need to remove duplicate entries from a comma delimited string. Simply call the function with the input string and the delimiter, and it will return the modified string with duplicate entries removed.

We encourage our readers to contribute their own scripts and solutions. If you have any SQL Server-related scripts or ideas, feel free to send them to us. We will publish them with your name, giving you credit for your contribution.

Thank you, Ashish Jain, for sharing this useful script with us. We appreciate your contribution!