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

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

sql-serversql-server-2008


Solution 1:[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.

Solution 2:[2]

You can try converting the field to a varchar first:

SELECT DISTINCT CONVERT(VARCHAR(MAX), Subjects) FROM T1

Solution 3:[3]

USE CAST

select distinct cast(Subjects as varchar(max)) from T1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 AGB
Solution 3

Related Questions