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'