I would like to see how many times the field MSGTEXT
is repeated in the table MMOUTBOUND
. For that I use the following query:
SELECT
MSGTEXT,
COUNT(*) TotalCount
FROM MMOUTBOUND
GROUP BY MSGTEXT
HAVING COUNT(*)>1;
But I get an error because ntext data types cannot be compared or sorted. How can I achieve this for the ntext data type?
You can't directly, for the entire column. However, indirectly, you can convert the first N characters and group by this instead, e.g.
SELECT CONVERT(NVARCHAR(100), MSGTEXT), COUNT(*) TotalCount
FROM MMOUTBOUND
GROUP BY CONVERT(NVARCHAR(100), MSGTEXT)
HAVING COUNT(*)>1;
As others have noted, note that you should convert your NTEXT
columns to NVARCHAR(MAX)