主页 / dba / 问题 / 151550
Accepted
Philᵀᴹ
Asked: 2016-10-07 00:11:58 +0800 CST

SQL Server - NTEXT 列和字符串操作

  • 772

我有一个表，其中有一NTEXT列名为comments. 我有第二个字符串，我们称它为anothercomment(a varchar)，它需要放在commentsword 之后的给定字符串中UPDATEHERE。

转换为nvarchar(max)截断comments字符串，所以我不能使用CHARINDEX()(之类的Msg 8152, Level 16, State 10, Line 2 String or binary data would be truncated.)。我曾经datalength()检查过有几千列 > 8000 个字符。

我想要实现的一个示例（尽管字符串更长）：

注释 -This is a test UPDATEHERE This is the end of the test

另一条评论——. This is inserted.

结果字符串 - This is a test UPDATEHERE. This is inserted. This is the end of the test

我意识到这对于普通的varchar()/来说是微不足道的nvarchar()，但却ntext是一个彻头彻尾的噩梦。我意识到这是一种已弃用的数据类型，但我没有编写有问题的应用程序。

sql-server sql-server-2008-r2

2 个回答

  1. 从代码的角度来看，转换nvarchar(max)和返回ntext确实让生活变得更简单，但这确实意味着转换和重写整个（可能非常大的）值，这意味着所有的 CPU 和日志记录开销。

    另一种方法是使用UPDATETEXT. 这已被弃用，就像 一样ntext，但它可以显着减少日志记录开销。不利的一面是，这意味着使用文本指针，并且一次只能操作一行。

    以下示例代码使用游标来解决该限制，并使用PATINDEX而不是CHARINDEX因为前者是直接使用的少数函数ntext之一：

    样本数据

    CREATE TABLE dbo.PhilsTable
(
    comment ntext NULL,
    anothercomment nvarchar(50) NULL
);

INSERT dbo.PhilsTable
    (comment, anothercomment)
VALUES 
(
    CONVERT(ntext, 
        N'This is a test UPDATEHERE This is the end of the test ' + 
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)), 
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext, 
        N'This is a test UPDATEHERE This is the end of the test ' + 
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)), 
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext, 
        N'This is a test UPDATEHERE This is the end of the test ' + 
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)), 
    CONVERT(nvarchar(50), N'. This is inserted.')
);

    光标声明

    DECLARE c 
    CURSOR GLOBAL 
    FORWARD_ONLY 
    DYNAMIC 
    SCROLL_LOCKS 
    TYPE_WARNING
FOR
SELECT
    TxtPtr = TEXTPTR(PT.comment),
    Src = PT.anothercomment,
    Offset = PATINDEX(N'%UPDATEHERE%', PT.comment) + LEN(N'UPDATEHERE') - 1
FROM dbo.PhilsTable AS PT
WHERE
    PT.comment LIKE N'%UPDATEHERE%'; -- LIKE works with ntext

OPEN c;

    处理循环

    DECLARE 
    @Ptr binary(16),
    @Src nvarchar(50),
    @Offset integer;

SET STATISTICS XML OFF; -- No cursor fetch plans

BEGIN TRANSACTION;

    WHILE 1 = 1
    BEGIN
        FETCH c INTO @Ptr, @Src, @Offset;

        IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
        IF @@FETCH_STATUS = -1 BREAK; -- no more rows

        IF 1 = TEXTVALID('dbo.PhilsTable.comment', @Ptr)
        BEGIN
            -- Modify ntext value
            UPDATETEXT dbo.PhilsTable.comment @Ptr @Offset 0 @Src;
        END;
    END;

COMMIT TRANSACTION;

CLOSE c; DEALLOCATE c;
    • 10
  2. Best Answer

    nvarchar(max)除非你做错了什么，否则转换为应该工作CHARINDEX()

    试试这个代码片段，它应该输出你想要的。

    -- Create the table
CREATE TABLE [dbo].[PhilsTable](
    [comment] [ntext] NULL,
    [anothercomment] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

GO

-- insert very long string
INSERT INTO [dbo].[PhilsTable] (comment, anothercomment) VALUES (N'This is a test UPDATEHERE This is the end of the test' + REPLICATE (CAST(N'x' AS nvarchar(max)), 1000000), 'this goes in here');

-- verify data
SELECT DATALENGTH(comment), *  FROM [dbo].[PhilsTable];

-- perform replace
SELECT CAST(REPLACE(CAST(comment AS NVARCHAR(MAX)),'UPDATEHERE','UPDATEHERE' + anothercomment) AS NTEXT) FROM [dbo].[PhilsTable];

DROP TABLE [dbo].[PhilsTable];

    感谢Andriy M提供的帮助REPLICATE。

    • 8

相关问题