[fix]-The text, ntext, and image data types
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
Scenario:
In general, NTEXT type is used for varying-length Unicode string, TEXT type is used for varying-length non-Unicode string and IMAGE types for varying-length binary data as follows:
Errors:
Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 306, Level 16, State 2, Line 2
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
Fix:
Typecast the column with varchar or nvarchar as follows:.
SELECT CAST([Summary] AS NVARCHAR(4000)) AS [Summary], COUNT(*)
FROM [dbo].[DischargeSummary]
GROUP BY CAST([Summary] AS NVARCHAR(4000))
With SQL Server 2005 or SQL Server 2008 (or later), instead of VARCHAR(4000), the NTEXT column can be converted to NVARCHAR(MAX):
SELECT CAST([Summary] AS NVARCHAR(MAX)) AS [Summary], COUNT(*)
FROM [dbo].[DischargeSummary]
GROUP BY CAST([Summary] AS NVARCHAR(MAX))
Finally, to say, TEXT, NTEXT or IMAGE types can be converted to VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX), respectively.
Fixes are applicable to the following versions of SQL Server:
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Error Tags:
- Grouptext, ntext, and image data types cannot be compared or sorted
- sql - text, ntext, and image data types cannot be compared or sorted
- How do I get by with "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
- MSSQL How to fix error The text, ntext, and image data types cannot be compared or sorted
- Error in SQL Query The text, ntext, and image data types cannot be compared or sorted
- The text, ntext, and image data types cannot be compared or sorted but cannot be compared or sorted, except when using IS NULL or LIKE operator.
- SQL server the text ntext and image data types are not invalid for local variables.
