--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
|
--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
XML parsing: line 1, character 23, illegal name character The & is a reserved/special character in XML. It should be & 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