Deleting Duplicate Data from NText Column in SQL

Deleting Duplicate Data from NText Column in SQL

Using Row_Number function we can delete the duplicate Data easily but when your column Data type in NText then you have to cast the Ntext data type to nvarchar first then we can delete the data.

Below we will create a sample table with duplicate data and will show how to delete duplicate data when column type is NText.

--table with duplicate row
CREATE TABLE #employee (ID INT PRIMARY KEY IDENTITY(1,1), Name NTEXT); 

INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Hary'); 
INSERT INTO #employee (Name) VALUES ('Hary'); 

SELECT * FROM #employee; 


If you see the Output the Table has Name -Tom 3 times and Hary 2 times

Now we will use Row_Number function to delete the Duplicate Records.

--CTE to delete duplicate data 
WITH CTE AS ( 
SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS DupRow 
FROM #employee 
) 
--select * from cte
DELETE FROM CTE WHERE DupRow > 1; 

Above query will throw an error

Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

So in order to delete data from Ntext column we have to cast or convert the Ntext column to nvarchar


--CTE to delete duplicate data having NText column
WITH CTE AS ( 
SELECT ROW_NUMBER() OVER(PARTITION BY CAST(Name AS NVARCHAR(4000)) ORDER BY id) AS DupRow,* 
FROM #employee 
) 
DELETE FROM CTE WHERE DupRow > 1;

SELECT * FROM #employee;

Output

ID Name
1 Tom
4 Hary