Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Remove Duplicate Entry from Comma Delimited String’

I had to migrate data from old system to new system and I found that we have one field called ‘notes’ having duplicated comma separated IDs. The next stage was to remove the duplicated (IDs) from string (notes column).
We can develop this solution via while Loop but I developed it without Loop.

Given below is the solution that can remove duplicate entry from comma, semi colon or any other delimited string .

Create Function dbo.[UDF_Remove_Duplicate_Entry]
(
      @Duplicate_String VARCHAR(MAX),
      @delimiter VARCHAR(2)
)
RETURNS VARCHAR(MAX)
BEGIN
     DECLARE @Xml XML
     DECLARE @Removed_Duplicate_String VARCHAR(Max)
     SET @Duplicate_String=REPLACE(@Duplicate_String,'&','And')
     SET @delimiter=REPLACE(@delimiter,'&','And')

     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,'')

      SET @Removed_Duplicate_String=REPLACE(@Removed_Duplicate_String,'And','&')           
RETURN (@Removed_Duplicate_String)
END
GO

--For Example :
SELECT dbo.[UDF_Remove_Duplicate_Entry] ('1,2,2,3,4,1,1,2,3,5',',') AS [Comma Delimited]
GO
SELECT dbo.[UDF_Remove_Duplicate_Entry] ('1;2;2;3;4;1;1;2;3;5',';') AS [Semi Colon Delimited]
GO
--OUTPUT

Comma Delimited
——————
1,2,3,4,5

(1 row(s) affected)

Semi Colon Delimited
——————
1;2;3;4;5

(1 row(s) affected)

Advertisements

Read Full Post »