Menu
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).
415
ntext will always store its data in a separate database page, while nvarchar(max) will try to store the data within the database record itself. So nvarchar(max) is somewhat faster (if you have text that is smaller as 8 kB). I also noticed that the database size will grow slightly slower, this is also good.
Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
Text - Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. nText - Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text. text.
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' 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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
