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?

`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) - stop using `Ntext` right now - replace it with `nvarchar(max)`, and then you'll be able to compare and sort on those columns, too! — marc_s, Dec 18 '13 at 14:41
+1 for not using `(N)Text` wherever possible. Related SO artical comparing the differences: [link](https://stackoverflow.com/questions/2133946/nvarcharmax-vs-ntext) — JoeF, Jan 14 '20 at 19:16

score 14 · Accepted Answer · answered Dec 18 '13 at 14:42

14

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)

answered Dec 18 '13 at 14:42

StuartLC

Note that `marc_s` solution should be preferred to this workaround on `NTEXT`. NVARCHAR(MAX) can be grouped directly, even for `NVARCHAR` lengths of > 4000. [Fiddle here](http://www.sqlfiddle.com/#!6/aa596/2) – StuartLC Dec 18 '13 at 15:49

1 Answers1