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

sql, sql-server

Solution

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

Problem

email belongs to table booking and its defined as type "Text" in our Microsoft sql server ``` SELECT email, COUNT(email) AS NumOccurrences FROM Booking GROUP BY email HAVING ( COUNT(email) > 1 ) ``` after running the above query(trying to find duplicates emails in the booking) I got the error message like this: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. I am using Microsoft Sql

Original source