How to Distinct or Group by a Text (Or Ntext) in SQL Server 2005

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

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

This is a documented way to increase the string length beyond 4,000:

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

A similar trick applies to varchar().

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

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.

SQL: Grouptext, ntext, and image data types cannot be compared or sorted

No, they can't. Additionally, they're deprecated in favour of (n)varchar(max) types.

If you need to group them, either change your data structure from (n)text to (n)varchar(max) or do a convert in your group clause

 GROUP BY ProdID, CONVERT(nvarchar(max), ItemDes)

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

since you are using SQL Server, why not change the data type to VARCHAR(100)?

To work around this error without changing the datatype, the TEXT or NTEXT column needs to be converted to VARCHAR or NVARCHAR when used in either the ORDER BY clause or the GROUP BY clause of a SELECT statement. eg, which is alittle bit messy

SELECT  CAST(email AS NVARCHAR(100)) email, 
        COUNT(CAST(email AS NVARCHAR(100))) AS NumOccurrences
FROM    Booking
GROUP   BY CAST(email AS NVARCHAR(100))
HAVING  COUNT(CAST(email AS NVARCHAR(100))) > 1 

• SQL Server Error Messages - Msg 306

Union Causing The ntext data type cannot be selected as DISTINCT because it is not comparable

Change: '' AS 'DSO1',
to
NULL as 'DS01',

Empty set can't be converted to numeric in SQL server; which is required based on the datatype defined in the top select of the union.

I assume this is the problem because the top select in the union will return a numeric value. Since empty set can't be cast to a number, either provide a number (0) or use null.

Keep in mind when executing a union the number of columns must match and their data types must match. If they don't you get errors such as this.

As to the comment, "I don't understand why since neither of the main queries use a distinct" A UNION executes a distinct on the resulting unioned results. UNION ALL will not. This is why execution of a UNION ALL performs faster, as it doens't have to do the duplicate removal contained within the execution of a UNION.

SQL: Select distinct while ignoring one column

Cast pr.notes to VARCHAR(MAX) or NVARCHAR(MAX) depending of data that it holds (I assume that you use SQL Server - it looks that this is the case when I see your code).

    SELECT * FROM (

        select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,
        t.Subject,t.CreatedDate,CAST(pr.notes AS NVARCHAR(MAX)) AS notes, 
        RANK() OVER ( PARTITION BY  N.FullName ORDER BY t.CreatedDate DESC ) AS iRank

        from Dial d
        join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
        join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
        join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
        join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
        join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
        join database.dbo.dm_projects pr on pr.fk_applicationid = T.fk_applicationid
    where c.FK_ClientID in (39157,39160)

    ) AS t 
    WHERE t.iRank = 1;

EF: The text data type cannot be selected as DISTINCT because it is not comparable

Simple answer is "don't use text".

It was deprecated for varchar(max) years ago when SQL Server 2005 was released.

The code you have is issuing SELECT DISTINCT.

You need to fix the model/tables so it isn't text datatype

SQL Distinct - Not working

You should convert the data type of the column to varchar for distinct to work: CONVERT(VARCHAR(MAX), column).

try:

select distinct CONVERT(VARCHAR(MAX), SName), CONVERT(VARCHAR(MAX), major)
from Student, Apply  
where Student.sID = Apply.sID;

text, ntext, and image data types cannot be compared or sorted

COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

SELECT COALESCE(field1, field2, field3, field4) FROM yourtable

COALESCE

Or You can use CASE Statement

SELECT CASE WHEN field1 IS NULL THEN Field2
            WHEN Field1 IS NULL AND Field2 IS NULL Then Field3
            WHEN Field1 IS NULL AND Field2 IS NULL AND Field3 IS NULL Then Field4
            ELSE 'NIL'
            END

---

Related Topics