文本、ntext 和图像数据 >类型不能比较或排序,除非使用 IS NULL 或 LIKE >操作员 [英] The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
问题描述
电子邮件属于预订表,在我们的 Microsoft sql 服务器中定义为文本"类型
email belongs to table booking and its defined as type "Text" in our Microsoft sql server
SELECT email,
COUNT(email) AS NumOccurrences
FROM Booking
GROUP BY email
HAVING ( COUNT(email) > 1 )
运行上述查询后(尝试在预订中查找重复的电子邮件)我收到如下错误消息:
after running the above query(trying to find duplicates emails in the booking) I got the error message like this:
text、ntext 和 image 数据类型不能比较或排序,除非使用 IS NULL 或 LIKE操作员.
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
我正在使用 Microsoft Sql
I am using Microsoft Sql
推荐答案
既然你用的是SQL Server
,为什么不把数据类型改成VARCHAR(100)
?
since you are using SQL Server
, why not change the data type to VARCHAR(100)
?
要在不更改数据类型的情况下解决此错误,在 ORDER BY 中使用时,
TEXT 或 NTEXT
列需要转换为 VARCHAR 或 NVARCHAR
子句或 SELECT
语句的 GROUP BY
子句.例如,有点乱
To work around this error without changing the datatype, the TEXT or NTEXT
column needs to be converted to VARCHAR or NVARCHAR
when used in either the ORDER BY
clause or the GROUP BY
clause of a SELECT
statement. eg, which is alittle bit messy
SELECT CAST(email AS NVARCHAR(100)) email,
COUNT(CAST(email AS NVARCHAR(100))) AS NumOccurrences
FROM Booking
GROUP BY CAST(email AS NVARCHAR(100))
HAVING COUNT(CAST(email AS NVARCHAR(100))) > 1
- SQL Server 错误消息 - 消息 306
这篇关于文本、ntext 和图像数据 >类型不能比较或排序,除非使用 IS NULL 或 LIKE >操作员的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!