Is there any way to DISTINCT or group by a text (or ntext) in SQL Server 2005?

Question

In a table, I have a column called MEMO_TEXT that is a text data type. When I try creating a view and use a GROUP BY, I get the following error:

SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I get this error when I try to do a DISTINCT on the query as well. Any ideas on how to get around this?

If you need any more information, please let me know.

Answer 1

One hack around it is to cast it as an nvarchar(max)

Answer 2

Try these...

SELECT DistinctMemo = DISTINCT(CAST(MEMO_TEXT AS varchar(max)))
FROM   MyTable

-- or

SELECT DistinctMemo = CAST(MEMO_TEXT AS varchar(max))
FROM   MyTable
GROUP BY CAST(MEMO_TEXT AS varchar(max))

Answer 3

Can you just change the data type of the column to nvarchar(max)?

Consider putting in another column that takes the first 40 characters or so of your memo field, and group on that. Grouping on a memo field is going to be really slow if you have a lot of text in there.

UPDATE myTable SET myNewField = LEFT(myOldField, 40);

Answer 4

You might want to consider your DB design to see if there is a better way of implementing what you're after - a distinct compare on very large text fields could become a bit heavy.

Answer 5

Do you know that there will never be repeated data in the ntext field? You could do the distinct in a derived table on the other fields and then join to the table with the ntext field and grab it in the outer query.

something like (assume field3 is the ntext field)

select mt.idfield, a.field1, a.field2, mt.field3 
from mytable mt
join 
(select disitinct mt1.idfield, mt1.field1, mot.field2 from mytable mt1
join myothertable mot on mt1.idfield = mot.idfield) a 
   on a.ifield = mt.idfield