[fix]-The text, ntext, and image data types

Wikitechy | 1775 Views | sql server | 09 Jun 2016

 

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 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

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.




Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise