如何存储大于 varchar(max) 的字符串 var? [英] How to store a string var greater than varchar(max)?

问题描述

我正在尝试这样做:

声明@myVar VARCHAR(MAX)用光标循环选择@myVar = @myVar + bla bla bla结束循环

当循环结束时，@myVar 不完整，只包含 8000 个字符.

我尝试使用文本，但不允许使用本地变量.

这种情况有什么好的解决办法?

xml 变量?

我刚刚看了这篇帖子:

如何在 SQL Server 2000 中，如何传递大于 varchar(8000) 的字符串参数?

检查是否连接到一个varchar(max) 将超出允许的最大字符数

和其他人通过网络.

问候.

解决方案

Serious - VARCHAR(MAX) 可以存储多达 2 GB 的数据 - 而不仅仅是 8000 个字符......>

试试这个:

DECLARE @myVar VARCHAR(MAX) = ''声明@ix INT = 1当@ix <1000开始设置@myVar = @myVar + CAST('bla bla bla' AS VARCHAR(MAX))设置@ix = @ix + 1结尾选择数据长度(@myvar)

这将在 1000 次迭代后返回一个大于超过 8000 个字符的值.

重点是:如果你使用 varchar(max)，你需要确保总是 cast 你所有的字符串到 varchar(max) 明确 - 正如我在这个例子中所做的那样.否则，SQL Server 将退回到常规"varchar 处理，而且确实限制在 8000 个字符....

I'm trying to do this:

DECLARE @myVar VARCHAR(MAX)
Loop with cursor
select @myVar = @myVar + bla bla bla
end loop

When the loop ends, @myVar is incomplete, containing only 8000 characters.

I have tryed to use text, but is not allowed to local vars.

What would be a good solution to this case?

xml var?

I have just looked this posts:

How do I pass a string parameter greater than varchar(8000) in SQL Server 2000?

Check if concatenating to a varchar(max) will go beyond max allowable characters

And others through the web.

Regards.

解决方案

Seriously - VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....

Try this:

DECLARE @myVar VARCHAR(MAX) = ''

DECLARE @ix INT = 1

WHILE @ix < 1000
BEGIN
    set @myVar = @myVar + CAST('bla bla bla' AS VARCHAR(MAX))
    SET @ix = @ix + 1
END

SELECT DATALENGTH(@myvar)

This will return a value higher than 8000 characters after 1000 iterations.

The point is: if you're using varchar(max), you need to make sure to always cast all your strings to varchar(max) explicitly - as I did in this example. Otherwise, SQL Server will fall back to "regular" varchar processing, and that's indeed limited to 8000 characters....

这篇关于如何存储大于 varchar(max) 的字符串 var?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！