• Article

Modifying ntext, text, or image Values

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use varchar(max), nvarchar(max), and varbinary(max) data types instead. For more information, see Using Large-Value Data Types.

You can modify ntext, text, or image values by:

  • Using a database API such as ADO, OLE DB, or ODBC to execute an UPDATE or INSERT statement with a program variable bound to a parameter marker for the ntext, text, or image column. Then call the appropriate database API functions to send long data to the database one block at a time. DB-Library supports the same functionality with its text and image functions.

  • Using the WRITETEXT statement to rewrite the entire data value for the column.

    For example, this query changes the contents of the pr_info column for New Moon Books:

Note

To run the following examples, you will need to install the pubs database. For information about how to install the pubs database, see Downloading Northwind and pubs Sample Databases.

USE pubs;
GO
ALTER DATABASE pubs
SET RECOVERY BULK_LOGGED;
GO
DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(pr_info) 
FROM pub_info pr INNER JOIN publishers p
   ON p.pub_id = pr.pub_id 
   AND p.pub_name = 'New Moon Books';
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) ';
GO
ALTER DATABASE pubs 
SET RECOVERY FULL;
GO

  • Use the UPDATETEXT statement to update specific blocks of an ntext, text, or image column.

    For example, this query replaces the eighty-eighth character in the text column for New Moon Books (the second letter o in Moon) with the letter z:

USE pubs;
GO
ALTER DATABASE pubs SET RECOVERY BULK_LOGGED;
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
FROM pub_info pr 
INNER JOIN publishers p
    ON p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books';
UPDATETEXT pub_info.pr_info @ptrval 20 1 'z';
GO
ALTER DATABASE pubs SET RECOVERY FULL;
GO