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

What is the longest value you currently/might have in that column? — Martin Smith, Dec 10 '13 at 12:46

Aaron Bertrand · Accepted Answer · 2022-04-26T20:09:29.210

15

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.

edited Apr 26 '22 at 20:09

answered Dec 10 '13 at 12:38

Aaron Bertrand

272,866
37
466
490

1

Or possibly `VARCHAR(50)` would be more than sufficient looking at the example values. – Martin Smith Dec 10 '13 at 12:45
@MartinSmith What if I have to store text whose lenght is 110288? – Kamran Dec 10 '13 at 13:56
2

@Kami - Then you would need `varchar(max)` then. That supports around 2 billion characters. Quite unusual to need to use `DISTINCT` on such long strings though. – Martin Smith Dec 10 '13 at 14:02
It's not always possible to change a field type on a legacy database without refactoring and/or breaking unknown references to it. The number 1 answer shouldn't be a lesson in best practices without an actual answer to the question. – fix Apr 26 '22 at 19:42
1

@fix there's a big difference between "not always possible" and "never possible", especially 10 years later. Those legacy types have been deprecated for SEVENTEEN YEARS. In any case, I've updated the answer so maybe you could reconsider your vote and judge the advice in that context. Sometimes the best answer for the reader who wasn't aware the type is deprecated _is_ to fix it. No answer is the best or only answer for _every single user_. – Aaron Bertrand Apr 26 '22 at 20:10

score 14 · Answer 2 · answered Dec 10 '13 at 12:13

14

You can try converting the field to a varchar first:

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

answered Dec 10 '13 at 12:13

AGB

2,378
21
37

sumit · Answer 3 · 2013-12-10T13:25:24.370

3

USE CAST

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

edited Dec 10 '13 at 13:25

answered Dec 10 '13 at 12:14

sumit

15,003
12
69
110

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

3 Answers3

Linked