74

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).

Alex Angas
  • 59,219
  • 41
  • 137
  • 210
noober
  • 4,819
  • 12
  • 49
  • 85
  • convert it to NVARCHAR(MAX) and all the string functions will be available to you... NTEXT is messy, it's deprecated - get rid of it! – marc_s Aug 23 '10 at 06:18
  • 3
    If anyone asks M$ to convert their CRM DB's NTEXTs to NVARCHARs, I will sign the petition ;) – noober Aug 23 '10 at 08:21

2 Answers2

81

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'
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 1
    Unfortunately, it's not an option, since MS uses ntext on its own, though it declares it deprecated the same time. I'll try both N prefix and casting, thanks for the answer. – noober Aug 22 '10 at 22:11
20

If you would prefer not to cast, you can get by in some scenarios using LIKE or PATINDEX, as demonstrated on this MSDN thread: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6bd4c661-ea0b-435f-af78-097e61549d41

The LIKE expression, without wildcards, would be (in this case) roughly equivalent to a test for equality.

In this case, the expression would be:

[ntext2] NOT LIKE '1,032.5'
kbrimington
  • 25,142
  • 5
  • 62
  • 74
  • LIKE requires the DB to be properly indexed (AFAIK). I'd like to avoid this reqirement. – noober Aug 23 '10 at 08:25
  • @noober: LIKE clauses don't benefit much from indexes, except in the case where they do not contain leading wildcards. A cast will likely disregard an existing index. If you are concerned about performance, it would be in your best interest to performance test each of the options (cast, like, patindex) with and without an index. If I get a moment today, I'll check it out. – kbrimington Aug 23 '10 at 11:26
  • No, I just tested LIKE with NTEXTs on different DBs, and it failed with one of them. Just couldn't be executed. The problem was something with indices. So I cannot be sure that LIKE will work everywhere. – noober Aug 23 '10 at 13:34
  • @noober: I'd be interested in seeing the error, if you don't mind posting it. – kbrimington Aug 23 '10 at 13:36
  • Oops... it works now. Very odd. If I see this again, I'll let you know. – noober Aug 24 '10 at 15:16
  • Hello kbrimington, the problem was with CONTAINS, not LIKE. LIKE works fine. Regards, – noober Aug 27 '10 at 10:21