Monday, June 11, 2012

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

SQL Server Error Message :
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.


Example SQL Code 1:
[...] ORDER BY TableColumn
change to
[...] ORDER BY cast(TableColumn as varchar(500))


Example SQL Code 2:
[...] GROUP BY TableColumn
change to
[...] GROUP BY cast(TableColumn as varchar(500))