Select the field as Distinct having data type as Text. Sql Server

Question

Is their any way to select a field as Distinct whose data type is Text.

If we have a table T1 with a field named Subjects have data type as Text.

Subjects
--------
Room
--------
Room
--------
window
--------
Door
--------
Door
--------

If I try this query

Select Distinct (Subjects)
from T1

It gives me this error:

The text data type can not be selected as DISTINCT because it is not comparable

When I use Group by it give me this error:

The data types 'text', 'ntext' and 'image' can be compared or sorted, 
except when using IS NULL or LIKE operator.

Is there any solution ? Thanks

Answer 1

You can use:

SELECT DISTINCT CONVERT(varchar(max), text_column) ...

Or for less memory usage, if you're happy with the first x bytes (say, 900):

SELECT DISTINCT LEFT(text_column, 900) ...

While the cast/convert answers work, and while it's questionable to want to perform a distinct operation on data this large in the first place, the real fix is to stop using the TEXT data type. It has been deprecated since 2005. You should be using VARCHAR(MAX) instead, for a whole variety of reasons.