Modifying ntext, text, or image Values

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:

USE pubs
sp_dboption 'pubs', 'select into/bulkcopy', 'true'
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) '
sp_dboption 'pubs', 'select into/bulkcopy', 'true'

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
sp_dboption 'pubs', 'select into/bulkcopy', 'true'
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 88 1 'z' 
sp_dboption 'pubs', 'select into/bulkcopy', 'false'

Modifying ntext, text, or image Values

See Also