SQL Server:当列为 NTEXT 时 IN ('asd') 不起作用 [英] SQL Server: IN ('asd') not working when column is NTEXT

问题描述

我该如何解决这个问题?

How can I fix this problem?

where someNtext IN ('asd',asd1')

给出错误:

消息 402，级别 16，状态 1，行 XXXXX
数据类型 ntext 和 varchar 在等于运算符中不兼容.

Msg 402, Level 16, State 1, Line XXXXX
The data types ntext and varchar are incompatible in the equal to operator.

推荐答案

IN 列表只是 OR 条件的简写.LIKE 子句适用于 NTEXT 和 TEXT 字段.所以，你可以结合这两个想法来做到这一点:

An IN list is just short-hand for OR conditions. The LIKE clause works with NTEXT and TEXT fields. So, you can combine those two ideas to do this:

WHERE (
       someNtext LIKE N'asd'
OR     someNtext LIKE N'asd1'
      )

然而，正如@marc_s 在对问题的评论中所建议的，NVARCHAR(MAX) 是首选，因为所有字符串函数都可以使用它(以及 TEXT、NTEXT 和 IMAGE 数据类型自 SQL Server 2005 起已被弃用).您可以进行内联转换，例如:

However, as @marc_s suggested in a comment on the Question, NVARCHAR(MAX) is preferred as all string functions work with it (and the TEXT, NTEXT, and IMAGE datatypes have been deprecated as of SQL Server 2005). You could do an inline convert such as:

WHERE CONVERT(NVARCHAR(MAX), someNtext) IN (N'asd', N'asd1')

但可能不会像使用带有 OR 条件的 LIKE 子句那样执行.

but likely that would not perform as well as using the LIKE clause with OR conditions.

请注意:使用 NTEXT/NVARCHAR/NCHAR/XML 时> 数据，最好总是用大写的N"前缀字符串文字.不这样做可能会导致与数据库的默认排序规则相关联的代码页不支持的任何字符的数据丢失.

Please note: When working with NTEXT / NVARCHAR / NCHAR / XML data, it is best to always prefix string literals with an uppercase "N". Not doing so can result in data loss for any characters not supported by the code page associated with the default collation of the database.

有关在 SQL Server 中使用排序规则/编码/Unicode/字符串的更多信息，请访问:https://Collat​​ions.Info/

For more information on working with collations / encodings / Unicode / strings in general in SQL Server, please visit: https://Collations.Info/

这篇关于SQL Server:当列为 NTEXT 时 IN ('asd') 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！