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
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