blocks|key|800479|text|一种方法是将其转换为一个nvarchar(max)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|800480|这是一种将字符串长度增加到4,000以上的记录在案方法：|800481|nvarchar+[+(+n+%7C+max+)+]
可变长度Unicode字符串数据。N定义字符串长度，可以是1到4,000之间的值。max表示最大存储大小为2%5E31-1字节(2GB)，存储大小(以字节为单位)是输入%2B2字节数据的实际长度的两倍。nvarchar的ISO同义词是国家字符的变化和民族特征的变化。|blockquote|BOLD|800482|类似的技巧也适用于varchar()。|800483|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/es-es/library/ms176089.aspx|1^0|C|D|0|L|4|0|0|0|O|1U|Q|0|9|9|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@$9|10|A|11|1|12]]|E|$]]|$1|H|3|I|5|J|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|K]]|D|@]|E|$]]|$1|L|3|M|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|N|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|O|$P|$5|Q|R|S|E|$T|U]]|V|$5|Q|R|S|E|$T|U]]]]

One hack around it is to cast it as an <code>nvarchar(max)</code>.

This is a <a href="http://technet.microsoft.com/es-es/library/ms176089.aspx" rel="noreferrer">documented</a> way to increase the string length beyond 4,000:

<blockquote>
 <code>nvarchar [ ( n | max ) ]</code>
 
 Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.
</blockquote>

A similar trick applies to <a href="http://technet.microsoft.com/es-es/library/ms176089.aspx" rel="noreferrer">varchar()</a>.

blocks|key|800496|text|试试这些..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|800497|SELECT+DistinctMemo+=+DISTINCT(CAST(MEMO_TEXT+AS+varchar(max)))
FROM+++MyTable

--+or

SELECT+DistinctMemo+=+CAST(MEMO_TEXT+AS+varchar(max))
FROM+++MyTable
GROUP+BY+CAST(MEMO_TEXT+AS+varchar(max))|code-block|syntax|javascript|800498|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try these...

<pre><code>SELECT DistinctMemo = DISTINCT(CAST(MEMO_TEXT AS varchar(max)))
FROM MyTable

-- or

SELECT DistinctMemo = CAST(MEMO_TEXT AS varchar(max))
FROM MyTable
GROUP BY CAST(MEMO_TEXT AS varchar(max))
</code></pre>

blocks|key|800547|text|您能将列的数据类型更改为nvarchar(max)吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|800548|考虑添加另一列，其中包含备注字段的前40个字符，并对其进行分组。如果你有大量的文本，那么在备注字段上分组将会非常慢。|800549|UPDATE+myTable+SET+myNewField+=+LEFT(myOldField,+40);|code-block|syntax|javascript|800550|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Can you just change the data type of the column to nvarchar(max)?

Consider putting in another column that takes the first 40 characters or so of your memo field, and group on that. Grouping on a memo field is going to be really slow if you have a lot of text in there.

<pre><code>UPDATE myTable SET myNewField = LEFT(myOldField, 40);
</code></pre>

blocks|key|2326653|text|您知道在ntext字段中永远不会重复数据吗？您可以在派生表中对其他字段执行不同的操作，然后使用ntext字段连接到表中，并在外部查询中获取它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2326654|类似于(假设field3是ntext字段)|2326655|select+mt.idfield,+a.field1,+a.field2,+mt.field3+
from+mytable+mt
join+
(select+disitinct+mt1.idfield,+mt1.field1,+mot.field2+from+mytable+mt1
join+myothertable+mot+on+mt1.idfield+=+mot.idfield)+a+
+++on+a.ifield+=+mt.idfield|code-block|syntax|javascript|2326656|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Do you know that there will never be repeated data in the ntext field? You could do the distinct in a derived table on the other fields and then join to the table with the ntext field and grab it in the outer query.

something like (assume field3 is the ntext field)

<pre><code>select mt.idfield, a.field1, a.field2, mt.field3 
from mytable mt
join 
(select disitinct mt1.idfield, mt1.field1, mot.field2 from mytable mt1
join myothertable mot on mt1.idfield = mot.idfield) a 
 on a.ifield = mt.idfield
</code></pre>

In a table, I have a column called MEMO_TEXT that is a text data type. When I try creating a view and use a GROUP BY, I get the following error:

<blockquote>
 SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
</blockquote>

I get this error when I try to do a DISTINCT on the query as well. Any ideas on how to get around this? 

If you need any more information, please let me know.

Is there any way to DISTINCT or group by a text (or ntext) in SQL Server 2005?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在一个表中，我有一个名为MEMO_TEXT的列，它是一个文本数据类型。当我尝试创建视图并使用GROUP BY时，我会得到以下错误：Server数据库错误:无法比较或排序文本、ntext和图像数据类型，除非使用的是NULL或LIKE操作符。当我试图对查询执行一个不同的操作时，我也会得到这个错误。关于如何绕过这件事有什么想...

问是否有任何方法在Server 2005中通过文本(或ntext)来区分或分组？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有任何方法在Server 2005中通过文本(或ntext)来区分或分组？EN