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'