Changing ntext, text, or image Data

These are ways to update ntext, text, or image values in a row when replacing the entire value:

• Specify relatively short amounts of data in an UPDATE statement in the same way char, nchar, or binary data is.
• Use the Transact-SQL WRITETEXT statement. For more information, see WRITETEXT.
• ADO applications can use the AppendChunk method to specify long amounts of ntext, text, or image data. For more information, see Using Long Data Types.
• OLE DB applications can use the ISequentialStream interface to write new ntext, text, or image values. For more information, see BLOBs and OLE Objects.
• ODBC applications can use the data-at-execution form of SQLPutData to write new ntext, text, or image values. For more information, see Managing text and image Columns.
• DB-Library applications can use the dbwritetext function. For more information, see Text and Image Functions.

Microsoft® SQL Server™ also supports updating only a portion of an ntext, text, or image value. In DB-Library this can be done using the dbupdatetext function. For more information, see dbupdatetext. All other applications and Transact-SQL scripts, batches, stored procedures, and triggers can use the UPDATETEXT statement to update only a portion of an ntext, text, or image column.

This script shows using UPDATETEXT in conjunction with PATINDEX to find and replace a specific string in a text value:

USE Northwind

GO

CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)

GO

INSERT INTO TextParts

   VALUES( 1,

           'Sample string START TAG Text to go END TAG Trailing text.')

GO

DECLARE @PtrVar BINARY(16)

DECLARE @InsertPos INT

DECLARE @DeleteLen INT

SELECT @PtrVar = TEXTPTR(ColB),

       @InsertPos = (PATINDEX('%START TAG%', ColB) + 9),

       @DeleteLen = (

                      PATINDEX('%END TAG%', ColB) -

                      ( PATINDEX('%START TAG%', ColB) + 9

                              + 2 /* allow for blanks */ )

                    )

FROM TextParts

WHERE ColA = 1

UPDATETEXT TextParts.ColB

           @PtrVar

           @InsertPos

           @DeleteLen

           WITH LOG

           'The new text'

GO

SELECT * FROM TextParts

GO

The result set from the final SELECT statement is:

ColA        ColB

----------- ------------------------------------------------------------

1           Sample string START TAG The new text END TAG Trailing text.

To update data using UPDATETEXT