Translate to Tamil

Earn Money


Best Offers

Amazon Best Offers

Offers

Best Online Offers

Amazon Offers

Earn Money Short URL

Saturday, 31 October 2020

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

No comments:
Write comments

Contact form

Name

Email *

Message *

Infolinks

© 2014