By juliana | April 22, 2008
Msg 306, Level 16, State 2, Line 1
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.
When comparing (e.g. using LIKE or = ), convert the non text/ntext INTO text. One assumes it’d be something like this:
[...] WHERE TextDataTypeColumn like 'test%'
[...] WHERE TextDataTypeColumn like cast('test%' as text)
Because you’re converting from a “smaller” data type to a larger data type, you never have to face the possibility that data may be trunctaed.
I believe sorting will only work on varchar, so there’s no way around it.
When sorting, you’ll have to convert non text/ntext INTO varchar (and remember to give enough or as much space possible). One assumes it’d be something like this:
[...] ORDER BY TextDataTypeColumn
[...] ORDER BY cast(TextDataTypeColumn as varchar(500))