Search code examples
sqlsql-servert-sqlntext

What's the right way to compare an NTEXT column with a constant value?

If I use something like

[ntext2] <> '1,032.5',

I get this error:

The data types ntext and varchar are incompatible in the not equal to operator.

The best possible solution would be if comparison is implemented in the same way for any column type. (<> operator is applicable for both NVARCHAR and INT).

Solution

  • The ntext data type is deprecated in favour of the nvarchar(max) data type. If you can change the data type in the table, that would be the best solution. Then there is no problem comparing it to a varchar literal.

    Otherwise you would have to cast the value before comparing it:

    cast([ntext2] as nvarchar(max)) <> '1,032.5'

    You might also consider using a nvarchar literal, which solves some similar data type problems:

    cast([ntext2] as nvarchar(max)) <> N'1,032.5'