SQL Server - NTEXT columns and string manipulation

Converting to nvarchar(max) and back to ntext does make life simpler from a code point of view, but it does mean converting and rewriting the whole (perhaps very large) value, with all the CPU and logging overhead that implies.

An alternative is to use UPDATETEXT. This is deprecated, just like ntext, but it can reduce the logging overhead significantly. On the downside, it means using text pointers, and it only operates on one row at a time.

The following example code uses a cursor to work around that limitation, and uses PATINDEX instead of CHARINDEX since the former is one of the few functions that work directly with ntext:

Sample data

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.')
);

Cursor declaration

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;

Processing loop

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;

Converting to nvarchar(max) should work unless you are doing something wrong with your CHARINDEX()

Try this code snippet, it should output what you want.

-- 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];

Thanks go out to Andriy M for helping out with the REPLICATE statement.