如何更新 SQL Server 2000 中的 text 或 ntext 字段 [英] How to update a text or ntext field in SQL Server 2000
问题描述
所以我需要更新一个文本字段.UPDATE 语句或 WRITETEXT 语句在下面使用时都不起作用
So I need to update a text field. Neither the UPDATE statement or the WRITETEXT statement work when used below
CREATE TABLE MyTable (IDField int, MyField text)
INSERT INTO MyTable (IDField) SELECT 1
DECLARE @Data1 varchar(8000), @Data2 varchar(8000), @ptrval binary(16)
SELECT @Data1 = REPLICATE('1',8000)
SELECT @Data2 = REPLICATE('2',8000)
-- this sets MyField to string of only 8000 characters
UPDATE MyTable SET MyField = @Data1 + @Data2 WHERE IDField = 1
SELECT @ptrval = TEXTPTR(MyField )
FROM MyTable
WHERE IDField = 1
-- this causes an error: Incorrect syntax near '+'.
--WRITETEXT MyTable.MyField @ptrval @Data1 + @Data2
当局部变量不能是 TEXT 类型时,我应该怎么做?(如果我有 SSQL Server 2005,我会使用 varchar(max) - 但我没有)
How am I supposed to do this when local variables cannot be of type TEXT? (If I had SSQL Server 2005 I would use varchar(max) - but I don't)
推荐答案
尝试改用 UPDATETEXT
Try using UPDATETEXT instead
WRITETEXT MyTable.MyField @ptrval @Data1
UPDATETEXT MyTable.MyField @ptrval 8000 NULL @Data2
插入偏移量从零开始,因此 8000 应该写入第 8001 个字符.删除偏移量为空值,因为值为 NULL 会删除从 insert_offset 位置到现有文本末尾的所有数据.
The insert offset is zero based so 8000 should write into the 8001st character. The delete offset is null as a value of NULL deletes all data from the insert_offset position to the end of the existing text.
参考:http://msdn.microsoft.com/en-us/图书馆/ms189466.aspx
不要忘记 nvarchar(您应该与 ntext 字段一起使用)的最大容量是您正在使用的 varchar 字段的一半,因此在这种情况下,您的块大小需要减少到 4000.
Do not forget nvarchar (which you should use with ntext field) have a maximum capacity of half the varchar fields that you are using so your block sizes need to be reduced to 4000 in that case.
这篇关于如何更新 SQL Server 2000 中的 text 或 ntext 字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!