GROUP BY for ntext data

Question

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?

Answer 1

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)