How to remove the duplicate words in Sql Server

--DROP TABLE tbl_Sample --GO CREATE TABLE tbl_Sample (  [ID] INT IDENTITY(1,1),  [Sentence] VARCHAR(MAX) ) GO INSERT INTO tbl_Sample VALUES ('This is the the test test script from from friendlylearn.com') GO INSERT INTO tbl_Sample VALUES ('This should should remove duplicates') GO GO INSERT INTO tbl_Sample VALUES ('1 1 2 2 3 3 4 4 5 5')

--DROP FUNCTION dbo.[UDF_Remove_Duplicate_Entry] --GO CREATE FUNCTION dbo.[UDF_Remove_Duplicate_Entry] (       @Duplicate_Word VARCHAR(MAX) ) RETURNS VARCHAR(MAX) BEGIN       DECLARE @Xml XML       DECLARE @Removed_Duplicate_Word VARCHAR(MAX)       SET @Xml = CAST(('<A>'+REPLACE(@Duplicate_Word,' ','</A><A>')+'</A>') AS XML)         ;WITH CTE AS (       SELECT       ROW_NUMBER() OVER(ORDER BY A) AS [Sno],       A.value('.', 'varchar(max)') AS [Column]       FROM @Xml.nodes('A') AS FN(A) )         SELECT @Removed_Duplicate_Word =(SELECT Stuff((SELECT '' + ' ' + '' + A.[Column] FROM CTE A       LEFT JOIN CTE B ON A.[Sno]+1=B.[Sno]       WHERE (A.[Column]<>B.[Column] Or B.[Sno] is NULL)       FOR XML PATH('') ),1,1,''))         RETURN @Removed_Duplicate_Word END GO   SELECT [ID] ,[Sentence] As [Before Duplicate removal] ,dbo.[UDF_Remove_Duplicate_Entry]([Sentence]) As [After Duplicate removal] FROM tbl_Sample GO --OUTPUT May be if your using long table and If you get below error XML parsing: line 1, character 23, illegal name character The & is a reserved/special character in XML. It should be &amp; I'll even add that XML parsing: line 1, character 23, illegal name character is quite clear. If you consider that SQL counts from 1, then the 23rd character is the & Just add replace funtion remove spcial character SELECT [ID] ,[Sentence] As [Before Duplicate removal] ,dbo.[UDF_Remove_Duplicate_Entry](Replace(Sentence,'&','')) As [After Duplicate removal] FROM tbl_Sample