The Text, Ntext, and Image Data > Types Cannot Be Compared or Sorted, Except When Using Is Null or Like > Operator

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

How to fix SQL Error [306] [S0002]: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator?

In your query:

SELECT * FROM dbeplanningv3.dbo.usulan_dpr
WHERE  CONVERT(VARCHAR, evaluasi) is null 
or 
trim(CONVERT(VARCHAR, evaluasi)) = '' ORDER BY [detail] ASC OFFSET 0 ROWS  
FETCH NEXT 10 ROWS ONLY

only ORDER BY [detail] could cause this error, so I will assume [detail] is of type text (this column isn't visible on your screenshot). To avoid the error, you should convert it to varchar(max):

SELECT * FROM dbeplanningv3.dbo.usulan_dpr
WHERE  CONVERT(VARCHAR, evaluasi) is null 
or 
trim(CONVERT(VARCHAR, evaluasi)) = '' ORDER BY convert(varchar(max), [detail]) ASC OFFSET 0 ROWS  
FETCH NEXT 10 ROWS ONLY

But the important question is why on SQL Server 2012 you still use text data type? You should convert these to varchar(max) and avoid casting them all the time.

Also, this cast CONVERT(VARCHAR, evaluasi) is null is pointless. You can check evaluasi is null directly.

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

It took me some time to find out why the errors were thrown as our DDL has NO text data types for any of the columns, as you can tell from the SQL file mentioned above.

Thanks for everyone's attention and time. It turns out this is the root cause:
Very strange SQL Server behavior: automatically convert most varchar data types to text

I understand this will not apply to others' case but I recorded it here just in case anyone runs into the same situation I did... Moral of the story: never trust the software you are using. In this case, even our DDL has no text data types, MSSQL somehow converted most of the varchar types to text in 5, 10 minutes or hours after we ran the DDL/SQL script to create those tables, in a random fashion.

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

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)

---

Related Topics