Changing ntext, text or image Data
When you replace the whole value, you can update ntext, text, or image values in a row in the following ways:
- Specify relatively short amounts of data in an UPDATE statement just like the char, nchar, or binary data is specified.
- Use WRITETEXT.
- ADO applications can use the AppendChunk method to specify long amounts of ntext, text, or image data.
- 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 (Transact-SQL).
SQL Server 2005 also supports updating only a part of an ntext, text, or image value. In DB-Library you can do this using the dbupdatetext function. All other applications and Transact-SQL scripts, batches, stored procedures, and triggers can use the UPDATETEXT statement to update only a part of an ntext, text, or image column.
The following script shows using UPDATETEXT 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.
See Also
Concepts
Changing Data by Using UPDATE
Changing Data by Using a Cursor
Other Resources
Downloading Northwind and pubs Sample Databases