Wednesday, 16 January 2013

Remove Duplicate string with delimiter using SQL Server


Create Function dbo.Remove_Duplicate_Entry
(
      @Duplicate_String VARCHAR(MAX),
      @delimiter VARCHAR(2)
)
RETURNS VARCHAR(MAX)
BEGIN
     DECLARE @Xml XML
     DECLARE @Removed_Duplicate_String VARCHAR(Max)
     SET @Xml = cast(('<A>'+replace(@Duplicate_String,@delimiter,'</A><A>')+'</A>') AS XML)

     ;WITH CTE AS (SELECT A.value('.', 'varchar(max)') AS [Column]
      FROM @Xml.nodes('A') AS FN(A))

      SELECT @Removed_Duplicate_String =Stuff((SELECT '' + @delimiter + '' + [Column]  FROM CTE GROUP BY [column]
      FOR XML PATH('') ),1,1,'')
     
RETURN (@Removed_Duplicate_String)
END

No comments:

Post a Comment