SQL Operator Equal to Equal (=) For NText Datatype

February 25, 2012 4:46 pm
Andri Kasta Marengga

SQL Operator Equal to Equal (=) For NText Datatype

This morning, I’ve got a problem with using equal operator (=) in transact-SQL for ntext datatype column. I try this SQL command text


SELECT Name FROM MyTable WHERE Remarks='Test Row'

And it returns this error message “The data types text and varchar are incompatible in the equal to operator.”

After some googling, I found a fact that comparison operator doesn’t match with ntext, text, and image datatype column and equal (=) is one of comparison operator. Below are the complete list of comparison operator:

  • = (Equals)
  • > (Greater Than)
  • < (Less Than)
  • >= (Greater Than or Equal To)
  • <= (Less Than or Equal To)
  • <> (Not Equal To)
  • != (Not Equal To)
  • !< (Not Less Than)
  • !> (Not Greater Than)

To outsmart this problem, I convert my ntext field to nvarchar.


SELECT Name FROM MyTable WHERE CONVERT(NVarchar(MAX),Remarks)='Test Row'
-- make sure your table has no rows where Remarks is longer than 4K chars
-- or 8000 characters if the type is text

You may use LIKE operator, but I think it may not always deliver a “correct” value

SELECT Name FROM MyTable WHERE Remarks like 'Test Row'

, , ,

No comments yet.

Leave a Reply