SQL UDF - Remove Duplicates

a. Remove duplicates from a delimited string.

Example: after string_agg you get strings as 'ABC,DEF,GHI,ABC,PQR,GHI'

Here abc and ghi are duplicates

Below function can be used to remove the duplicates.

CREATE FUNCTION [dbo].[func_remove_agg_dups] 
    (   
    @DelimitedString varchar(8000),
    @Delimiter varchar(100)
    )

RETURNS varchar(1000)
AS
BEGIN
    DECLARE 
@Index smallint,
@Start      smallint,
@DelSize    smallint,
@tmpValue varchar(100),
@tmpPos int,
@uniqueValues varchar(255),
@numOfValues int

SET @DelSize = LEN(@Delimiter)
SET @tmpPos = 0
SET @numOfValues = LEN(@DelimitedString) - LEN(REPLACE(@DelimitedString,@Delimiter,''))
SET @uniqueValues = ''
IF @numOfValues = 0

BEGIN
SET @uniqueValues = @DelimitedString
END

ELSE
BEGIN
SET @numOfValues = @numOfValues + 1
WHILE @tmpPos < @numOfValues
BEGIN
SET @tmpPos = @tmpPos + 1
SET @tmpValue = [dbo].[func_string_splitter](@DelimitedString,@Delimiter,@tmpPos)
IF CHARINDEX(@tmpValue,@uniqueValues) = 0

BEGIN
SET @uniqueValues = CONCAT(@tmpValue,@Delimiter,@uniqueValues)
END
END
END
RETURN rtrim(@uniqueValues,@Delimiter)
END
GO

Usage:

select [dbo].[func_remove_agg_dups] ('abc,def,ghi,jkl,abc',',')
jkl,ghi,def,abc

select [dbo].[func_remove_agg_dups] ('abc;def;ghi;jkl;abc',';')
jkl;ghi;def;abc

This function uses another UDF - func_string_splitter - to split string based on delimiter and return the positional value.